Re: vlookup returns #N/A but value exists
Dave R.
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.
"SteveMcD >" <<SteveMc...@excelforum-nospam.com> wrote in message
news:SteveMc...@excelforum-nospam.com...
> I have a column of numeric and alphanumeric codes which I am trying to
> look up in a sorted table on a different worksheet in order to return
> the value in column 2 of the table. Most of the codes exist in the
> 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 correct
> value is returned from the table. Can you suggest what might be going
> wrong?
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>
Eric
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
Peo Sjoblom
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