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 .Transcribed image text:
2When shipping products two major costs are incurred: the cost of insuring 3 the item and the actual freight cost. Insurance is dependent on thevalue of 4 the shipment S 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 OR CO HI Regular $ 5.00 $ 6.00 $ so ao S S00 6.00 S 60 500.. 8.00 Express-l s-_7.60S-8.4015-7.604_4.70 $ 7.60 $ 8.40 i s 8.40 i $ 7.60 $ 13.50 Overnite S 11.60 S13.70 S11.60 7.80 11.6013.70 $13.70 11.60 $17.10 Shipping Insurance Shipping Insurance Cost 10 | OrderID ShipTo ' ProductID | Product Name Value Cost Freight Cost Total Shipment Insurance Value Cost 1003 AZ 12 1004_WA 1007 NV 14 1011_ UT 1027CA 100 QuickStove 100 Quic 100 QuickStove 100 QuickStove 100 QuickStove 102 Replacement Fuel Cells 104 Quick Stove Can 104 Quick Stove Can 104 | QuickStove Can 102 Replacement Fuel Cells 104 Quick Stove Can 102 Replacement Fuel Cells 104 Quick Stove Can S 68.80 Expres 2.50 $ 86.00 Express 2.50 120.39 Regular 3.00 120.39_Express $ 3.00 $ 17.55 Overnite 1.00 $ 4.00 overnite S 0.50 $ 69.98 Overnite 2.50 1004| WA | kStove $0.50 $101.00 $30 2.00 $50 2.50 S 100 $ 3.00 15 161041MT 200 S 4.00 1042 CA 1050 OR 1058UT 1059 AZ 1060 WA 1062 UT 23| 1073 co 18 19 20 $ 274.32 | Overnite $274.32 Express i s 4 00 | $400 5.00 $ 4.00 $ 15.68Regular \ 1.00 69.98 Overnite 2.50 60.80 Regular2.50 $ 34.99 Overnite 2.00 $ 19.60 Overnite 1.00 $_ 4.00 Overnite $0.50 24 1074102 Replacement Fuel Cells 25 1088 WA 102 Replacement Fuel Cells