When VLOOKUP can't find a value in a lookup table, it returns the #N/A error. ... This can cause problems when the lookup table contains actual zero values, because it suggests that blank cells in the lookup table also contain zeros, when they in fact are empty.
Why is my VLOOKUP giving me 0?
VLOOKUP function retrieves a 0 value when the value in column C is an empty cell. To convert 0 to an empty string we can use LEN and IF functions. LEN function has the result 0 for the blank cells.
Why does VLOOKUP work in one cell but not another?
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.Sep 16, 2014