Using the VLOOKUP Function - Exercise

Contact Us or call 1-877-932-8228
Using the VLOOKUP Function - Exercise

Using the VLOOKUP Function

Duration: 15 to 25 minutes.

In this exercise, you will use the VLOOKUP function to automatically fill in the description and price of items on an invoice based on the item number.

  1. Open VLOOKUP.xlsx from your Excel2013.2/Exercises folder and go to the sheet named "Invoice".
  2. Use the VLOOKUP function to query the Description and Price from the table located in the sheet named "Table". You will need to insert the VLOOKUP function into cells B7:B15 and D7:D15.
    1. Hint: Including absolute references when referring to the range will enable the formulas to be copied to other cells within the column.
  3. What is Item Number 135798 and what does it cost?
  4. What is Item Number 678452 and what does it cost?

Solution:

  1. To query the Description from the table located in the sheet named "Table":
    1. In the sheet named "Invoice", select cell B7.
    2. On the FORMULAS tab, in the Function Library group, click the Insert Function command:Insert Function Command
    3. In the Insert Function dialog box:
      1. Search on "VLOOKUP" or, in the Or select a category drop-down box, select Lookup & Reference.
      2. Under Select a function, select VLOOKUP.
      3. Click OK.VLOOKUP
    4. In the Function Arguments dialog box:
      1. Enter the Lookup_value: A7.
      2. Enter the Table_array: Table!A2:C9 (click the cell selection arrow, then the sheet named "Table", and then select the cells).
      3. Enter the Col_index_num: 2 (because the description is in the second column in the table).
      4. Enter the Range_lookup: False (because you require an exact match).
      5. Click OK.OK Option
  2. To query the Price from the table located in the sheet named "Table":
    1. In the sheet named "Invoice", select cell D7.
    2. On the FORMULAS tab, in the Function Library group, click the Insert Function command.
    3. In the Insert Function dialog box:
      1. Search on "VLOOKUP" or, in the Or select a category drop-down box, select Lookup & Reference.
      2. Under Select a function, select VLOOKUP.
      3. Click OK.
    4. In the Function Arguments dialog box:
      1. Enter the Lookup_value: A7.
      2. Enter the Table_array: Table!A2:C9 (click the cell selection arrow, then the sheet named "Table", and then select the cells).
      3. Enter the Col_index_num: 3 (because the description is in the third column in the table).
      4. Enter the Range_lookup: False (because you require an exact match).
      5. Click OK.
  3. Select cell B7 and edit the formula to make the table references absolute (change A2:C9 to $A$2:$C$9). Then do the same in cell D7.
  4. Copy cell B7 to cells B8:B15 and cell D7 to D8:D15.
  5. Enter "135798" into any cell under "Item Number" on the invoice. Item Number 135798 is a rake and costs $12.98.
  6. Enter "678452" into any cell under "Item Number" on the invoice. Item Number 678452 is a wrench and costs $6.99.
Next