The Excel VLOOKUP Function
Excel Vlookup Function Additional Resource
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.
Therefore, in addition to this page, which provides an overview of the Vlookup function, we also have produced a more in-depth Excel Vlookup Tutorial.
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:
VLOOKUP( lookup_value, table_array, col_index_num, [range_lookup] )
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:
If the function cannot find an exact match to the supplied lookup_value, it should use the closest match below the supplied value.
Note: if this option is used, the left-hand column of the table_array must be in ascending order.
|FALSE||-||If the function cannot find an exact match to the supplied lookup_value, it should return an error.|
In text-related Vlookups, where an exact match is required, the lookup_value can contain the following wildcard characters:
? - matches any single character
* - matches any sequence of characters
Example 1 - Vlookup Requiring an Exact Match
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.
| ||A||B||C||D||E|| |
|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|
|3||Tinned Tuna||$1.50|| || || || |
|4||Cornflakes||$3.50|| || || || |
|5||Shortcake Biscuits||$1.00|| || || || |
|6||Toothpaste||$4.10|| || || || |
|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.
In this example:
- The lookup_value is the text string "Cornflakes", which is located in cell D2;
- The table_array is defined as columns A-B of the spreadsheet;
- The col_index_num is set to 2, to denote that the value returned should be taken from column 2 of the table_array;
- The [range_lookup] argument is set to FALSE, to indicate that we only want a result to be returned if an exact match to the lookup_value is found.
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.
Example 2 - Vlookup Requiring the Closest Match
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.
| ||A||B||C||D||E||F||G|| |
|1||Lower||Upper||Grade|| ||Name||Score||Grade|| |
|2||0%||44%||F|| ||Anne||52%|| =VLOOKUP( F2, A2:C7, 3, TRUE )|| - returns the value "E"|
|3||45%||54%||E|| || || || || |
|4||55%||64%||D|| || || || || |
|5||65%||74%||C|| || || || || |
|6||75%||84%||B|| || || || || |
|7||85%||100%||A|| || || || || |
The above Vlookup function returns the grade for the score 52%, which is E.
In this example:
- The lookup_value is the value 52%, which is located in cell F2;
- The table_array is the range A2-C7 of the spreadsheet;
- The col_index_num is set to 3, to denote that the returned value should be taken from column 3 of the table_array;
- The [range_lookup] argument is set to TRUE, to indicate that, if an exact match to the lookup_value is not found, we want the closest value below the lookup_value to be returned instead.
(Note that, in this case, the first column of the table_array (i.e. cells A2-A7) must be in ascending order).
Further Vlookup Examples
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.
Vlookup Function Errors
If you get a Vlookup error this is likely to be one of the following:
Occurs if the Vlookup function fails to find a match to the supplied lookup_value.The cause of this will generally depend on the supplied [range_lookup] argument:
|if [range_lookup] = TRUE|
(or is omitted)
the #N/A error is produced if the smallest value in the left-hand column of the table_array is greater than the supplied lookup_value.
The #N/A error could also arise if the left column of the table_array is not in ascending order.
|if [range_lookup] = FALSE||-||the #N/A error is likely to be because an exact match to the lookup_value is not found in the first column of the table_array.|
If you still can't understand why you are getting this Vlookup error, check out the Vlookup #N/A Error page.
Occurs if either:
- The supplied col_index_num argument is greater than the number of columns in the supplied table_array
- The formula has attempted to reference cells that do not exist.
(This can be caused by relative referencing errors when the Vlookup is copied to other cells).
If you need further help with this Vlookup error, check out the Vlookup #REF! Error page.
Occurs if either:
- The supplied col_index_num argument is < 1 or is non-numeric
- The supplied [range_lookup] argument is non-numeric value and is not equal to TRUE or FALSE.
|For further Vlookup troubleshooting tips, there is an excellent Quick Reference Card available on the Microsoft Office website|