Question

Home InsertPage Layout Formulas Dat Lookup Functions 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 3 Hint: You will need to use a function within the HLOOKUP function to determine the fz -VLOOKUP(F11,E$6:NS8,H LOOKUP·ー [6 points] When shipping products two major costs are incurred: the cost of insuring theitem and the actual freight cost. Insurance is dependent on the value of Quickstove has an agreement with shippers for a fixed rate that is determined by destination state and service level (regular, express or 00 5.00 8.00 Shipping Insurance OrderlD Ship To ProductID Product Name Cost Freight Cost Total So $120.39 Regular 120.39 Express | $ 3.00|' #REF! 102 Replacement Fuel Cells $69.98 Overnite |$ 2.50 |' #REF! 104 Quick Stove Can 102 Replacement Fuel Cells s 15.68 Regular $69.98|Overnite | $ 2.50| #REFI 1060WA 102 Replacement Fuel Cells 60.80 Reular 2.50$6.00 102 Replacement Fuel Cells 102 Replacement Fuel Cells 102 Replacement Fuel Cells 102 Replacement Fuel Cells 102 Replacement Fuel Cells 1088WA 34.99 Overnite 2.00 102.87 Regular$ 3.00 S34.99Express 1262 UT 106 Stove In ACan 106 Stove In ACan 108 QuickStove Fuel Disk $ 398.89 Rela 4.00 S 37.85 Overnite $2.00 107 SIAC Fuel Cell 108 QuickStove Fuel Disk 101 QuickStove Can 1677 WA 101 QuickStove Can 108 QuickStove Fuel Disk 101 QuickStove Can 1718 OR 9.90 Overnite $ 2 110 Firebox Bundle 110 Firebox Bundle

Answer 1

az wa ca 10 solution VLOOKUP(F9,B4:K6,HLOOKUP(B9,C2:K3,2)) regular express overnite 7.6 11.6 8.4 13.7 7.6 11.6 4.7 7.8 7.6 11.6 8.4 13.7 8.4 13.7 7.6 11.6 13.5 17.1 1. Insert a row below row 2, numbering it as shown (this is done because we will be using vlook as well as hlookup simultaneously) 2.Hlookup statement inside the vlookup gives us a number between 2 to 10 3. this is done since in the vlookup statement requires a numeric value as third parameter 4. Based on "ship to" Hlookup throws us a number between 2 to 10. 5. Based on shipping class and the column number (that we get from the hlookup function lookup provides the freight cost 8 oid p name val 100 quickstov 68.8lexpr s class I cost f cost 2.5-VLOOKU 1003l az 10 12 13 15 16 17