Correcting a #N/A error within a vlookup formula

[Guest]

I have tried everything from formatting the cells to sorting the columns and
I have over 10,000 cells to pull from and it would be horrific to have to
type in each one. Any suggestions or tricks that people have found would be
much appreciated. Thanks for all your help!

 

[Nick Hodge]

Matt

Are you expecting other than #N/A? In other words are you sure the data you
are looking up is in the table. If this is the case then you may have one
of them formatted as text or similar and Excel is seeing them as different.
You may have some hidden characters, you can use the TRIM function to
address this.

If you are expecting #N/A then you can suppress it by wrapping it in an IF
function and ISNA, e.g

=IF(ISNA(Original_VLOOKUP),0,Original_VLOOKUP)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Guest]

Nick

I am not expecting #N/A. I am trying to match two 8 digit numbers from two
diferent worksheets in the same file using vlookup and pull the number in the
column next to the second 8 digit number on the second sheet. I simply
cannot get it to pull up the number. It comes back as #N/A. I tried
formatting them all in the same format 3 seperate times (text, number, and
general). Just can't get it to work.

 

[Simon Murphy]

Matt
Are you saying that numbers that look the same are not getting matched?
if so try multiplying them by 1 (use a temporary column or edit paste
special)(this makes sure they are numbers)
what Vlookup function are you using?
= vlookup(A1,sheet2!A1:B10000,2,false) or something? (the lookup table needs
to include the second column)
Post a bit more info of what you are doing and what is not working
The formattting should have no effect (unless 1 is text possibly)
cheers
Simon