Note: The data in the table should be sorted in ascending order (alphabetically A to Z).
The syntax for the VLOOKUP function is:
= VLOOKUP ( lookup_value , table_array , col_index_num , range_lookup )
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 Using Excel 2010's VLOOKUP Function:
For help with this example, see the image above.
- Enter the following data into the cells indicated:
D3 - Part
D4 - Bearing
D5 - Bolt
D6 - Cog
D7 - Gear
D8 - Washer
E3 - Price
E4 - $17.34
E5 - $1.54
E6 - $20.21
E7 - $23.56
E8 - $1.43
- Click on cell D1 and type the word Bolt. This is the part we are trying to price.
- Click on cell E2 - the location where the results - in this case, the price of a bolt - will be displayed.
- Click on the Formulas tab.
- Choose Lookup & Reference from the ribbon to open the function drop down list.
- Click on VLOOKUP in the list to bring up the function's dialog box.
- In the dialog box, click on the Lookup _value line.
- Click on cell D1 in the spreadsheet to tell VLOOKUP that we are looking for the price of bolts.
- In the dialog box, click on the Table_array line.
- Drag select 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.
- In the dialog box, click on the Col_index_num line.
- Type the number 2 to indicate that the data we want returned is in column 2 of the table_array.
- In the dialog box, click on the Range_lookup line.
- Type the word False to indicate that we want an exact match for our requested data.
- Click OK.
- In cell D1 of the spreadsheet, type the word bolt.
- The value $1.54 should appear in cell E1 displaying the price of a bolt as indicated in the table_array.
- When you click on cell E1, the complete function
= VLOOKUP ( D1 , D4:E8 , 2 , FALSE ) appears in the formula bar above the worksheet.