VLOOKUP error in Excel

Ever have this happen? You write your VLOOKUP formula and have one of the following values returned:

• #VALUE!
• #NAME?
• #N/A
• #REF!
• Other UNEXPECTED result!

Below you’ll find some common pitfalls that cause each of the errors above.

VLOOKUP function syntax

First, here’s a recap of the usage syntax for the VLOOKUP function in Excel. I’ll be referring to the parts in the explanations below.

VLOOKUP(lookup_value, table_array, column_index_number, [range_lookup])

For more detail on the arguments, see the function’s documentation page.

VLOOKUP errors in Excel

#VALUE! error causes in VLOOKUP

column_index_number is less than 1

In an effort to retrieve a value that is to the left of the lookup column, you may be tempted to put in a value less than one. However, VLOOKUP only finds values starting with the lookup column, and counting over to the right.

If you need to look up values that actually sit to the left of the lookup column, you can. Just use a combination of the INDEX and MATCH functions. To see how this is done, read “Do you need your VLOOKUP to look to the left instead of the right?“

Your table_array reference isn’t correct

If you’re using a named range, did you use the correct name? It’s worth double-checking to make sure you selected the correct range name.

Or, if you entered a range was it an absolute range or relative?

If you used A2:C5, and your VLOOKUP is filled down in a table, you will have A3:C6, A4:C7, etc. which is likely not what you expect or need. Instead, use the absolute reference form of $A$2:$C$5.

The lookup_value is longer than 255 characters

This is a limit within Excel.

An option is to use the INDEX and MATCH functions instead.

#NAME? error causes in VLOOKUP

This happens when you type the name of the function incorrectly. Basically, just make sure you spelled “VLOOKUP” correctly. That’s all.

#N/A error causes in VLOOKUP

The lookup_value does not exist in the lookup column

Did you have a typo? Double-check to be sure the value you’re searching is typed correctly.

It’s also possible that the value simply is not in the list. If you’d rather return something friendly than “#N/A” to your worksheet, use the ISNA function. I prefer simply to use IFERROR, but it catches all the errors and not just #N/A. You may have something like the following.

=IFERROR(VLOOKUP(lookup_value, table_array, column_index_value, [range_lookup]), "Value not found")

Leading or trailing spaces in your lookup_value data

If you think you may have leading or trailing spaces in your lookup_value data, you can use the TRIM function in Excel to remove them before and after your data. You may change your function call to something like the following.

=VLOOKUP(TRIM(lookup_value), table_array, lookup_column_value, [range_lookup])

The format of the lookup_value is not the same as the data

Does your data have the same number format as your lookup_value data? Are your numbers formatted as text. Data from a data source may be loaded into Excel as text — even though it looks like a number. If you think your lookup_values may be formatted as text, you can either change the number formatting of the column. Choose Number in the drop down list in the Number group in the ribbon.

You can also use the NUMBERVALUE function. It will convert text (even if it has commas in it) numbers into the numerical values. You may have the following in your VLOOKUP function.

=VLOOKUP(NUMBERVALUE(lookup_value), table_array, lookup_column_value, [range_lookup])

Special characters in the lookup_value or data

Carriage returns, etc can cause VLOOKUP problems. Be sure your data doesn’t contain special characters.

You’re not using the first column as the lookup column

The first column is always the lookup column. You cannot use a column to the left by providing a negative number for the column_index_number.

If you need to retrieve data to the left of your lookup column, you can. Just use a combination of the INDEX and MATCH functions. To see how this is done, read Do you need your VLOOKUP to look to the left instead of the right?

range_lookup not working as expected

Incorrect use of the range_lookup feature

The help text for range_lookup says to use TRUE for an “approximate match”. However, this has nothing to do with any sort of fuzzy matching of a text lookup. If you want to perform a fuzzy match for the VLOOKUP, see Fuzzy VLOOKUP in Excel for information.

Lookup data unsorted

If you are looking for a value nearest your lookup value, is your lookup column sorted in ascending order? This is required when range_lookup is TRUE.

lookup_value too small

If your lookup_value is less than the smallest value in the lookup column, the range_lookup will also fail when set to TRUE.

#REF! error causes in VLOOKUP

When you see this error returned, make sure the column_index_number is not greater than the number of columns in your table_array range.

Unexpected (non-error) value

Unexpected value returned? Here are some common causes.

You expected a case sensitive lookup

VLOOKUP does a case insensitive comparison. So, MYVALUE, MyValue, Myvalue, myvalue are all the same as far as VLOOKUP is concerned.

VLOOKUP only returns the first value found

VLOOKUP starts at the top and looks for your value. It will stop at the first value found.