Home »
ExcelVlookupTutorial »
VlookupError
Excel Vlookup Tutorial Part 5  Fix Your Vlookup Error
If you get a Vlookup error, the first step is to check that you have followed the
Vlookup Rules correctly.
If you are confident that your formula is correct, use the table below to identify the most likely cause of your error:
Common Vlookup Errors
#N/A 
 
Occurs if the Vlookup function fails to find a match to the supplied lookup_value.
The cause of this generally depends 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
lefthand 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 lefthand 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.

#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.
If you need further help with this Vlookup error, check out the
Vlookup #REF! 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
one of the logical values TRUE or FALSE.

Incorrect Value Returned 
 
If your Vlookup function is simply returning the wrong value, check the following:

Are the values you are searching in the left column of the table_array?
For the Vlookup function to work, the values that you are searching must be in the left column
of the table_array.

If the [range_lookup] argument is set to TRUE (or omitted), the
function will return the closest match below the lookup_value.
For this to work correctly, the left column of the table_array must
be in ascending order.

Check that the col_index_num argument refers to the required column.
Remember that this is the column number counting from the first column of the table_array.
It is not necessarly the same as the spreadsheet column number.

If the [range_lookup] argument is set to FALSE, the Vlookup function
requires an exact match. Check that there is only one match to the lookup_value
within the left column of your table_array. Note that if there is more
than one match, the Vlookup function will use the first match that it encounters.

For further Vlookup troubleshooting tips, in the form of a handy Vlookup Quick Reference Card, see the
Microsoft Office website.