Why Does VLOOKUP Return An #N/A Error?

VLOOKUP week enters its second day, meaning that it’s time to have a look at how to problem-shoot #N/A errors. On the online forums I frequently see frustrated users asking why their VLOOKUP formula returns an #N/A error, so I hope that some of the reasons I’ve listed below will be helpful to many of you.

Exact Match #N/A

By far and away the most common reason for an exact match #N/A error is that the lookup_value doesn’t exist in the lookup column. Sometimes you can be virtually certain that it does exist, but the difference can be extremely subtle so don’t be surprised if you’ve missed it. In case you’re wondering what “lookup column” means, it was defined in the previous post.

Here are three hard-to-spot examples :

• Date Mismatch

In this example we’re trying to find a date in a list and return the corresponding value in the adjacent column. As an aside, I should mention that I live in the UK so my dates are in a dd/mm/yyyy format. We can see that the date exists in the lookup column, so why is the exact match formula returning an #N/A error?

Appearances can be deceptive. The first thing to do is to check whether VLOOKUP is correct in that the two 03/01/2012 values are not the same. In a spare cell we can enter the simple formula =H2=E4 to check; it returns FALSE which  confirms that the dates indeed do not match:

Cell formatting can change the appearance of a cell without changing the underlying value. If we click into the cell we can see that, in this case, the date in the lookup column also has a time stamp of 02:24:00 :

The time stamp explains why VLOOKUP can’t marry the two dates together: they’re different. The correct resolution to this problem will vary depending on your situation: most likely you need to remove the times from the lookup column, which can be done very quickly using Text To Columns.

• Text Mismatch

Here’s a similar problem except that we have some sort of mismatch between two pieces of text rather than dates. They’re both spelt exactly the same, so why is VLOOKUP returning an #N/A error?

The first step is to perform a direct comparison just the same as we did last time. The formula =H2=E4 returns FALSE confirming that the two values are not the same. The next check to perform is whether or not the length of the two strings is the same. The formulas =LEN(H2) and =LEN(E4) reveal an interesting difference: the length of ADMIRAL GRP in the lookup column seems to be one character longer than the lookup_value:

This is because cell E3 has an extra space character after the word GRP. This can be clearly seen by clicking into the cell and looking at the position of the cursor which is not directly beside the letter P:

Once the extra space has been removed the two values will match.

• Type Mismatch

This difference is the most subtle of all. In the below table we have a VLOOKUP formula which should return Paul, but for some reason it’s returning an #N/A error:

Again, the direct comparison formula =H2=E4 returns FALSE, confirming that the pair of 3 values are not the same. Formatting could be hiding the problem – much the same as the date mismatch formula – but clicking in both the cells confirms that there are no decimal points in this particular case. On this occasion, the difference is caused by a data type mismatch. Excel recognises several data types including Number, Text, Logical, Error and Array. The data type can be checked using the TYPE function:

The TYPE formulae tell us that the 3 in the lookup column is actually “3” stored as text, whilst the lookup_value, 3, is a number. For more information on this have a look at the TYPE worksheet function topic in the Excel help file.

VLOOKUP is type sensitive which is why it considers the two to be different. In fact, it turns out that, in this case, all the numbers in the lookup column are numbers stored as text; they can be quickly converted into number types using Text To Columns.

Approximate Match #N/A

The issues above may also apply so they should be considered but here are a few other potential problems which speak for themselves:

• The lookup_value is less than the smallest value in the lookup column.

• The lookup column is not sorted in ascending order. This will cause varied results: sometimes you will get the correct value, sometimes you’ll get the wrong value and sometimes you’ll get an error.

If you’ve frequently experienced an #N/A value which isn’t covered by any of the above then post a comment and let us know!