Vlookup returns N/A despite of existing match

Question

I have two tables, table1 and table2. I execute VLOOKUP function in order to fill in 3 columns from table2 into table1.

For some reason, the formula doesn't work for the first row, and doesn't find the exact match from table2 even though it exists.

I made sure that both columns (for comparison) have the same format (General) and there is no extra spacing. Same conditions also apply for the rest of the records, and it works there properly.

table1 - you can see the missing matches for the first row.

table2 - you can see the match does exist, but it is not reflected in table1.

Is there any other reason why VLOOKUP can't find a match for a specific record?

Answer 1

I recently encountered the same issue and resolved it by changing the vlookup formula to =VLOOKUP([value to lookup], [lookup table], [column to return in the lookup table], False). Setting the last input argument to "false" forces Excel vlookup function to perform an exact match.

Answer 2

Have observed scenarios like this where direct comparison fails (e.g. formula =A1=B1 resulted in FALSE) and yet length =LEN(A1)=LEN(B1) and letter by letter ASCI comparison (=CODE(A1,1,1), =CODE(A1,2,1), =CODE(A1,3,1), etc.) shows no difference.

What worked was to adjust the format of the lookup value inside the VLOOKUP.

e.g. =VLOOKUP(A1, ARRAY, COL_NUM, FALSE) -> =VLOOKUP(TEXT(A1, "000"), ARRAY, COL_NUM, FALSE)

Answer 3

Here's an issue I encountered: my VLOOKUP formula returns the correct value (1) if I type in the value-to-look-up (1.016) directly in the formula, shown in cell F54.

However, if I referenced a value in column A as the value-to-look-up, the formula returned #N/A, as shown in cell F55.

(I was actually trying to VLOOKUP the current row's A value plus 0.015, i.e. VLOOKUP(A54+0.015, $A$3:$B$203, 2, FALSE))

Yet if I use the ROUND function, then the VLOOKUP formula works, as shown in F56.

Answer 4

Okay - Here's a doozy of a use-case. VLOOKUP and INDEX-MATCH were returning #N/A for values that were "apparently" equal. Cleaned my data with =TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))) and that didn't work.

Then, I compared two cells that looked like they had matching values and they evaluated to FALSE (A1=B1 resulted in FALSE).

Then, as a last resort, I code checked each ASCII value for each character in the two cells and I found that the "-" in one cell was different from the "-" in the other cell. The first cell has the ASCII value 63 and the second cell had the ASCII value 45 for what looked like was the same "-". Turns out that 63 is a "short dash" and 45 is your standard dash or minus symbol.

The way to evaluate the ASCII codes for each character in a string is to combine the CODE function with the MID or RIGHT functions after testing the cells for length using the LEN function.

Examples:

LEN(A1) should equal LEN(B1)

For the first character in each cell:

CODE(A1) Code defaults to the first character on the left
CODE(MID(A1,2,1) yields the ASCII for the second character
CODE(MID(A1,3,1) yields the ASCII for the second character
and so on

If you have a lot of characters you can post an integer sequence next to your CODE-MID function and point the position argument to the related integer and just copy down or across

Or

You can look for the weird non-numeric character and just test that one for both cells.

Answer 5

Try directly evaluating equality for the two cells that you believe are equal, for instance if A2 is the value you are looking up and Sheet2!A100 is the value you think should match try this in a cell:

=(A2=Sheet2!A100)

If that returns false then you know that there is some formatting issue or error in your vlookup.

Also try Formulas / Evaluate Formula ribbon command to step through your vlookup in case that highlights something wrong.