Vlookup & functions

  • Thread starter Thread starter Jd
  • Start date Start date
J

Jd

VLOOKUP keeps returning #N/A because the formatting of
the numbers is inconsistent or incorrect. I have
reformatted both sets of data several times and Excel
says they are the same format (either GENERAL or NUMBER)
but VLOOKUP will not work. I am fairly familiar with
VLOOKUP and have tried to test the formula using a small
set of control data that I know matches, yet it still
returns #N/A. I've tried to reformat, copy/paste
special/value/add, used the format painter and even
copied the same data but still returns #N/A.

Any help is much appreciated!!
 
This is a huge problem with Excel and VLOOKUP. After formatting all the
numbers consistently, copy a blank cell, select all your numbers, and Paste
Special | Add. Most likely VLOOKUP will work after that.
 
Thanks for the tip, I have even tried that and it's still
not working...any other ideas?!

Thanks!
 
What formula are you using, where do the values come from, did you copy and
paste them from the web, from a mainframe,
did you type in the values? There are many workarounds and I haven't yet
seen a vlookup that couldn't be fixed and even if they look the same they
are not the same. If the web is most likely you have invisible html
characters known as char(160).
 
I have had occasion to go to the re-formatted data cell; hit F2 and enter.
This works but will be a pain if you have a lot of data.

ED
 
I have had the same problem, and somebody suggested
setting the format to General or Number and hitting F2.
This seemed to have solved all my problems.
 
Back
Top