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 value that you want to look for, in the left-hand column of the supplied data array
table_array
-
The data array or table, that you want to search the left hand column of, for the supplied lookup_value
col_index_num
-
The column number, within the supplied array, that you want the corresponding value to be returned from
[range_lookup]
-
An optional logical argument, which can be set to TRUE or FALSE, meaning :
TRUE
-
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 range_lookup is set to TRUE, 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
Vlookup Examples
Vlookup Example 1
In the spreadsheet below, columns A and B list an inventory of grocery items, and their prices,
and 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.
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 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
Vlookup Example 2
In the spreadsheet below, columns A-C 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.
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 value returned 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 to use the closest value below the lookup_value
Further Vlookup Examples
For a practical example of the Vlookup function being used to create a variable chart, see the
Excel Variable Chart page.
Although there is no VBA Vlookup function, you can call the Excel Worksheet Vlookup Function from VBA.
See the VBA Vlookup page for more details.
Trouble Shooting
If you get a Vlookup error this is likely to be one of the following :
Common Vlookup Errors
#N/A
-
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 likely to be because the smallest
value in the left-hand column of the table_array is greater than the supplied lookup_value
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 left-hand 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
#VALUE!
-
Occurs if either:
-
The supplied col_index_num argument is < 1 or is not recognised as
a numeric value
or
-
The supplied range_lookup argument is not recognised as TRUE or FALSE
#REF!
-
Occurs if either:
-
the supplied col_index_num argument is greater than the number of columns in
the supplied table_array
or
-
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
