Skip to Content

Why Does VLOOKUP Return #N/A When Value Exists? (Examples)

The VLOOKUP formula is a popular function for getting the value from a tabular data set and has 3 required parameters and an optional fourth, and looks something like this:

=VLOOKUP(reference_value, data, returning_column_value, [is_sorted])

The first parameter reference_value is the value you are searching for in the first column of your data set (the second parameter). The third parameter returning_column_value is the column from your data set that you wish to return, and the optional fourth parameter is_sorted is a boolean type where if this is true (by default it is) then the returned value from this formula will be its first match, otherwise if FALSE then returns the closest match.

Here’s an example of the VLOOKUP in action in this simple spreadsheet example:

ABC
1IDEmployee NameSalary
21001Smith, John85,000
31010Doe, Jane105,000
41011Doe, John60,000
5100185,000
=VLOOKUP(A5, A1:C4, 3)
A simple example of the VLOOKUP formula at work, using a cell reference as the first parameter

So if we look at the above example, and reference a value that doesn’t exist in the first column, what happens?

ABC
1IDEmployee NameSalary
21001Smith, John85,000
31010Doe, Jane105,000
41011Doe, John60,000
51#N/A
=VLOOKUP(A5, A1:C4, 3)
When referencing a VLOOKUP formula on an ID that does not work, we get an error that the value 1 could not be found

It may be obvious from this small example to see that I don’t have the value 1 in the ID column, but what happens when you can clearly see that the value you’ve entered into the first parameter is in fact there?

Having used VLOOKUP so many times, here is how I use to solve the #N/A problem:

Check Reference Value Data Type

It can sometimes appear as though the number I have in the reference_value parameter clearly matches the values in the data set I’m searching, however, what might appear to be a number when looking at the spreadsheet may actually be text!

Therefore to get around this problem I convert the first parameter to match the data type values. For example, if the first column of data has all of its first column values as numbers then I change the reference_value to be a number. If the data set’s first column is strings, then I make sure my first parameter in the VLOOKUP function is a string too.

Here are some examples of how I quickly convert the reference_value being passed in to match the data set.

# where A5 is text and the data set's first column is number values
=VLOOKUP(A5*1, A1:C4, 3)

# where A5 is a number and the data set's first column is text values
=VLOOKUP(A5&"", A1:C4, 3)

Another easier way could also be to select the cells comprising of the data set’s first column and to make them all the same data type (in Google Sheets select the Format menu item then Number then either Number or Plain Text).

Summary

Besides #REF and #VALUE errors which can easily be fixed when using the VLOOKUP formula, the lesser known, yet most frustrating error when using this formula is the #N/A error.

The most common reason for this type of error comes down to the different data types being used in both the first parameter of the VLOOKUP function and the first column in the data range being referenced.

To have this formula function properly you need to ensure they are of the same type.