Question

1.Use the VLOOKUP function in cell F9 to determine the number of days it will take to ship order 1. Use the distance in cell E9 to lookup the appropriate value on the "Delivery Information" reference table (range K10:M15). Make sure to use relative and absolute references appropriately.

2.Use the VLOOKUP function in cell G9 to calculate the delivery price for order 1. The delivery price can be referenced on the "Delivery Information" lookup table. Use appropriate relative and absolute cell references

3.Use the HLOOKUP function in cell H9 to calculate the product pricing based on the color and quantity of the billboards ordered. Product pricing can be referenced on the "Product Pricing" lookup table (range O9:Q18). Be sure to use appropriate relative and absolute cell references.

Answer 1

Answer: Here is a little explanation about the two functions: The VLOOKUPO function: The syntax of the fu VLOOKUP( value, table, indexnumber, [approximate match] ) Where, Value: Anything to be looked up. nction is: table: Two or more columns of data that is sorted in ascending order. index number: The column number in table from which the matching value must be returned [approximate match]: Holds FALSE to find an exact match or TRUE to find an approximate match In the first question: We need to find the number of days to ship the order Hence the formula should be: VLOOKUP E9,SK$10:SM$15,2,TRUE)

Here is the screen shot: f VLOOKUP(SE9,SK$10:M$15,2,TRUE) order color shipped distance days delivery(in s) price total delivery information product pricing d blue red $0 10 Blue 2 $30 3 $50 5 $75 7$150 8 $400 975 700 1100 800 1200 900 1350 1000 1425 1100 1550 Blue $0 867 16 17 Blue Blue Blue $0 174 12 13 Blue Blue Blue Blue Blue 21 $0 16 Blue Red Blue 19 23 24 Red Red 9 $0 In the second question: We need to find the delivery price Hence the formula should be VLOOKUP E9,SK$10:SM$15,3,TRUE)

Here is the screen shot: e-VLOOKUP(E9SKS10SMS15,3,TRUE) delivery information product pricing 400 150 20 Blue $0 3 $50 5 $75 7 $150 8 $400 Blue Blue Blue Blue 700 1100 800 1200 00 1350 1000 1425 1100 1550 16 17 150 12 Blue 13 Blue 14 Blue Blue Blue 174 50 30 17 16 Blue Red Blue 18 30 19 150 30 $0 150 24 Red The HLOOKUPO function: The syntax oi the function is HLOOKUP( value, table, index number, [approximate match]) Where, Value: Anything to be looked up table: Two or more rows of data that is sorted in ascending order index number: The row number in table from which the matching value must be returned approrimate match]: Holds FALSE to find an exact match or TRUE to find an approximate match

In the third question: We need to find the product pricing. Hence the formula should be HLOOKUP9SPS9:SQS18,VLOOKUP(D9,SOS9:SQS18,1)) Here is the screen shot: 丘 =HLOOKUP(C9,SPS9:sas18,VLOOKUP(09,SOS9:SQ$18,1)) order color shipped distance days deliverylin $) delivery information product pricing 800$0 1100 0 975 $0 1200o 800 $0 1000 o 12000 1000o 700 $0 shipped blue Blue Red 10 5 2 $300 $500 3 $400 $700 4 $500 $800 5 $600 $975 6 $700 $1,100 12 100 3 $50 200 5 $75 4007 $150 800 8 $400 Blue 867 $800 $1,200 16 Blue Blue 10 Blue 8 $900 $1,350 9 $1,000 $1,425 0 $1,100 $1,550 18 12 Blue 13 Blue 14 Blue 15 Blue 16 Blue 17 Red 18 Blue Red Blue 1425 0 700 $0 500 $0 800 $0 700 $0 1000o 800 $0 174 21 17 10 15500 20 1200o 700 $0 800 $0 1425 0 1100 SO 21 35 Red 9 Red 6 86