1. Technology
You can opt-out at any time. Please refer to our privacy policy for contact information.

Excel's Vlookup Function

Excel Vlookup Quick Tutorial


Find the Price of Items with VLOOKUP

Find the Price of Items with VLOOKUP

© Ted French

Related Tutorial: Step by Step version of Excel VLOOKUP tutorial including screen shots

Excel's VLOOKUP function, which stands for vertical lookup, is used to find specific information that has been stored in a spreadsheet table.

The VLOOKUP Function's Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for the VLOOKUP function is:

= VLOOKUP ( lookup_value , table_array , col_index_num , range_lookup )

lookup _value: The value you want to find in the first column of the table_array.

table_array: This is the table of data that VLOOKUP searches to find the information you are after.

The table_array must contain at least two columns of data. The first column contains the lookup_values.

col_index_num: The number of the column in the table_array that contains the data you want returned.

range_lookup: A logical value (TRUE or FALSE only) that indicates whether you want VLOOKUP to find an exact or an approximate match to the lookup_value. Typing False will return exact matches only.

Example: Find the Unit Price of Items

As seen in the image above, the example below covers how to find the unit price of items listed in a table of data.

Entering the Data

  1. Enter the following data into the appropriate cell:
  2. D1:    Part Name
    E1:    Price
    D2:    Bolt
    D4:    Part Name
    D5:    Bearing        
    D6:    Gear            
    D7:    Widget        
    D8:    Cog 
    E4:    Price  
    E5:    Bearing        
    E6:    Gear            
    E7:    Widget        
    E8:    Cog 

Entering the VLOOKUP Function

  1. Click on cell E2 - the location where the results will be displayed.

  2. Click on the Formulas tab.

  3. Choose Lookup & Reference from the ribbon to open the function drop down list.

  4. Click on VLOOKUP in the list to bring up the function's dialog box.

  5. In the dialog box, click on the Lookup _value line.

  6. Click on cell D1 in the spreadsheet to enter that cell reference into the dialog box. This is the cell where we will type the part name about which we are seeking information.

  7. In the dialog box, click on the Table_array line.

  8. Highlight cells D4 to E8 in the spreadsheet to enter that range into the dialog box. The table_array is the table of data that VLOOKUP searches for the lookup_value specified in cell D1.

  9. In the dialog box, click on the Col_index_num line.

  10. Type the number 2 to indicate that the data we want returned is in column 2 of the table_array.

  11. In the dialog box, click on the Range_lookup line.

  12. Type the word False to indicate that we want an exact match for our requested data.

  13. Click OK.

  14. In cell D1 of the spreadsheet, type the word Widget.

  15. The value $14.76 should appear in cell E1 displaying the price of a widget as indicated in the table_array.

  16. If you click on cell E1, the complete function = VLOOKUP ( D1 , D4:E8 , 2 , FALSE ) appears in the formula bar above the worksheet.

©2014 About.com. All rights reserved.