vlookup returns #N/A but value exists

  • Thread starter Thread starter SteveMcD
  • Start date Start date
S

SteveMcD

I have a column of numeric and alphanumeric codes which I am trying t
look up in a sorted table on a different worksheet in order to retur
the value in column 2 of the table. Most of the codes exist in th
table but only one returns a value; all the others return #N/A. If
however, I overtype the codes which are returning #N/A, the correc
value is returned from the table. Can you suggest what might be goin
wrong
 
I guess that the codes are numeric, and your formula is looking for text, or
vice versa. When you type a numeric in and press enter, that might change it
to an actual number which the formula will find.

You can copy some blank cell, select all the codes, edit>paste special>add,
and that should change them all into numerics.
 
Steve,

This happens me every now and then, especially when I have
imported numbers from a foreign source. Many times,
the "lookup range" is pulled in as text and the column you
reference in the VLOOKUP function is trying to compare
numbers to text.

I usually fix this by highlighting the imported column you
are using as a reference and choosing Data -> Text to
Columns without specifying any delimiters. For some
reason, Excels will convert these column correctly.

If that doesn't work, then you may have some spaces or
hidden characters in your source data. You may have to do
a little sleuthing to figure it out.

Hope that helps!

Eric
 
Hidden characters, wrong format would be the reason.
For instance if you would have an extra space a the end
that would be enough. Now when you go in and enter it,
it will work. Suggestions could be to use

=VLOOKUP(TRIM(lookupvalue,Table etc..

You might want to run a macro if you import these values

http://www.mvps.org/dmcritchie/excel/join.htm#trimall
--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
Thank you all for your responses.
Eric's solution has solved the problem.
Thanks once again

Stev
 
Back
Top