Question

Use the following Excel features in exercise 7: VLOOKUP, INDEX and MATCH.

Home 10 Insert Page Layout Formulas Data Arial 10 A- A = B I U 1 $A = * v fx в с D E F Review = = = = View air = = => Wrap Text =Merge & Center Paste F5 А G н т т к 5 Product Name 6 Product ID 30 Product Name 1 Paint Brush 4 Gallon of paint 5 Grass Seed 9 10' ladder 10 Vacuum 11 Chain Saw 15 Tool Set 19 Lawn mower 20 Step stool 21 Rake 25 Lawn chair 27 Plant stand 28 Axe 29 Shovel 23 Garden hose 14 light bulbs Unit Cost In Stock On Order Reorder Le Retail Price $18.00 39 0 25 $19.00 17 40 25 $10.00 70 25 18 $97.00 150 $31.00 0 0 45 $125.00 108 199 $45.00 05 89 $148.00 299 $15.50 $17.45 10 $39.00 $15.00 0 15 $16.50 3 20 $21.00 22 15 $9.00 610 25 $4.95 800 50 20

1. Use MATCH to determine what row Rake resides.
2. Use INDEX to display the name “Rake” for the row you have determined in part 1. Hint, you need the output from part 1 to accomplish part 2
3. Use a combination of INDEX and MATCH to display the product ID number of light bulbs in cell B6.
4. Using VLOOKUP, add a box under the VLOOKUP heading that displays the quantity in stock when you type in the product name into cell B5. Hint, you need the output for part 3 to accomplish part
5. Use a combination of INDEX and MATCH to display the name of the product that has a retail price of $199.

Answer 1

1) Use MATCH to determine what row Rake resides.

Answer)

Value	Result	Formula
Rake	10	=MATCH(I11,B10:B25,0)

2)Use INDEX to display the name “Rake” for the row you have determined in part 1.

Answer)

Result	Formula
Rake	=MATCH(I11,B10:B25,0)

3 Answer)

Product ID

14

=INDEX(A10:G25,MATCH("light bulbs",B10:B25,0),1)

4 Answer)

Product Name

80

=VLOOKUP(B6,A9:G25,4,0)

5 Answer )

Value	Result	formula
199	6	=MATCH(I9,G10:G25,0)

Result	Formula
Chain saw	=INDEX(B10:B25,6,0)