The Excel Vlookup function is one of Excel's most popular functions. However, although it is straightforward once you get to grips with it, many users initially have problems with this function.
The Excel VLOOKUP function 'looks up' a given value in the left-hand column of a data array (or table), and returns the corresponding value from another column of the array.
The syntax of the function is:
where the arguments are as follows:
|lookup_value||-||The value that you want to look up.|
|table_array||-||The data array or table, containing the search values in the left hand columns and the return values in another column.|
|col_index_num||-||The column number, within the supplied table_array, that you want to return a value from.|
An optional logical argument, that describes what the function should return in the event that it does not find an exact match to the lookup_value.
The [range_lookup] can be set to TRUE or FALSE, meaning:
Columns A and B of the spreadsheet below list an inventory of grocery items and their prices. In cell E2 of the spreadsheet, the Vlookup function is used to look up the price of an item from the inventory.
|1||Item Description||Cost ($)||Current Item:||Current Item Cost ($)|
|2||Tinned Tomatoes||$0.90||Cornflakes||=VLOOKUP( D2, A:B, 2, FALSE )||- returns the value $3.50|
|7||Tinned Baked Beans||$0.99|
|8||White Sliced Bread||$0.80|
The following Vlookup function looks up the value "Cornflakes" in column A of the example spreadsheet and returns the associated price from column B.
The above Vlookup function returns the price for "Cornflakes", which is $3.50.
Note that in this example, as the [range_lookup] argument is set to FALSE (requiring an exact match), it is not necessary to order the table_array by its first column.
Columns A-C of the spreadsheet below list the grades that are assigned to examination marks lying within the ranges 0-44%, 45%-54%, etc.
Cell F2 shows the score of 52% that was achieved by the student "Anne" in an examination. The Vlookup function in cell G2 looks up this score in column A of the spreadsheet and returns the associated grade from column C.
Note that, in this example, if the exact score of 52% is not found in column A, we want, instead, to use the nearest value below this score.
|2||0%||44%||F||Anne||52%||=VLOOKUP( F2, A2:C7, 3, TRUE )||- returns the value "E"|
The following Vlookup function looks up the score 52% in column A of the example spreadsheet and returns the associated grade from column C. (Note that, in this example, as the exact score of 52% is not present in column A, we want, instead, to use the nearest value below this score).
The above Vlookup function returns the grade for the score 52%, which is E.
For a practical example of the Vlookup function being used to create a variable chart, see the Excel Variable Chart page.
Also, there are several further Vlookup examples on the Microsoft Office website.
If you get a Vlookup error this is likely to be one of the following:
|For further Vlookup troubleshooting tips, there is an excellent Quick Reference Card available on the Microsoft Office website|