Use the HLOOKUP function in cell H11 to calculate freight cost based on the shipping class and state to which the shipment is going. Freight Cost can be referenced on the "Freight Cost" lookup table (range E5:N8). Be sure to use appropriate relative and absolute cell references.
Hint: You will need to use a function within the HLOOKUP function to determine the appropriate row. Please provide formula .

 

A B D E G H K. L 1 When shipping products two major costs are incurred: the cost of insuring the item and the actual freight cost. Insurance is dependent on the value of 4 the shipment 5 Quickstove has an agreement with shippers for a fixed rate that is 6 determined by destination state and service level (regular, express or 7 overnite). Freight Cost AZ WA NV UT CA MT OR CO HI 3.00 $ 5.00 $ 4.70 $ 7.60 $ 8.40 S Regular 5.00 $ 6.00 $ 5.00 $ 6.00 $ 5.00 ($ 8.00 8.40 $ 7.60o ($ 13.50 13.70 $ 13.70 I$ 11.60) $ 17.10 6.00 $ Express 7.60 $ 8.40 $ 7.60 $ Overnite 11.60 $ 13.70 $ 11.60 $ 7.80 $ 11.60 $ Shipping Insurance Shipping Insurance Cost 10 OrderiD Ship To ProductID Product Name Value Class Cost Freight Cost Total Shipment Insurance $ 68.80 Express $ 86.00 Express $ 120.39 Regular 11 1003 AZ 100 QuickStove 2.50 Value Cost 1004 1007 12 WA 100 QuickStove 2.50 $ 0.50 $ $ 120.39 Express $ 17.55 Overnite $ 4.00 Overnite $ 69.98 Overnite $ 274.32 Overnite $ 274.32 Express $ 15.68 Regular $ 69.98 Overnite $ 60.80 Regular $ 34.99 Overnite $ 19.60 Overnite $ 4.00 Overnite 13 NV 100 QuickStove 3.00 24 10 $ 1.00 UT 100 QuickStove 30 | $ 2.00 50 s 14 1011| 3.00 15 1027 CA 100 QuickStove 1.00 2.50 1041 102 Replacement Fuel Cells 104 Quick Stove Can 104 Quick Stove Can 104 Quick Stove Can 102 Replacement Fuel Cells 104 Quick Stove Can 16 MT 0.50 $ 100 $ 3.00 17 1042 CA 2.50 $ 200 $ 4.00 18 1050 OR 4.00 400 $ 5.00 19 1058 UT 4.00 20 1059 AZ 1.00 21 1060 WA 2.50 22 1062 UT 102 Replacement Fuel Cells 2.50 23 1073 CO 104 Quick Stove Can 2.00 1074 102 Replacement Fuel Cells 102 Replacement Fuel Cells 24 CO 1.00 25 1088 WA 0.50