Excel VLOOKUP #N/A from a table with similar values

0
votes

I'm doing a VLOOKUP =VLOOKUP(A2, Sheet2!$A$1:$O$400,2,FALSE) And running into a problem where the A2 value has very close values associated with different cells within the A column:

VLOOKUP for 1063:
Sheet2 has IW1063, C1063, 1063 (the one I want to vlookup), and 10634 in the A column.

The return is #N/A, even if the value is present in the table and in the lookup range. Could I have missed something or is there something I should add?

1
excelvlookup
is one a number stored as text and the other a number? If so they will not match. If the column in Sheet 2 is all text then use: =VLOOKUP(A2&"", Sheet2!$A$1:$O$400,2,FALSE) – Scott Craner
are columns in the same format, and, are you sure there's no space in one of them? – Isolated

1 Answers

0
votes

As Scott and Isolated mentioned please check if both columns are in same format and has no space difference in them.

Looking at the formula
=VLOOKUP(A2, Sheet2!$A$1:$O$400,2,FALSE) FALSE indicates an exact match, and could be the reason to return #N/A

=VLOOKUP(A2, Sheet2!$A$1:$O$400,2,TRUE) You can use TRUE which is an approximate match and the nearest value is picked and there are certain limitations to it like your lookup columns should be in ascending order to get correct values, I'm not sure about others.