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 values to return 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:
In text-related Vlookups, where an exact match is required, the lookup_value can contain the following wildcard characters:
Columns A and B of the spreadsheet below list an inventory of grocery items and their prices. Cell E2 of the spreadsheet shows a simple example of the Vlookup function being 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 above Vlookup function returns the price for "Cornflakes", which is $3.50.
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 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:
|#N/A||-||[range_lookup] argument:The cause of this will generally depend on the supplied|
|For further Vlookup troubleshooting tips, there is an excellent Quick Reference Card available on the Microsoft Office website|