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 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.

#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 TRUE or FALSE.
Incorrect
Value
Returned
-

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

  1. 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.

  2. 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.

  3. 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.

  4. 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. If there is more than one match, the Vlookup function will use the first match that it encounters.

Further Vlookup troubleshooting tips are provided, in the form of a handy Vlookup Quick Reference Card, on the Microsoft Office website.