excel vlookup not working correctly

[Guest]

I'm using vlookup in Excel 2003 to return exact matches from another sheet
and it has some error where it returns the #N/A error for a few values, even
though the matching target is present. I don't think it's user error -- I can
open the same file in Open Office Calc and all the values are correctly
returned. Is there any fix for this? I have all the current patches applied.

 

[Dave F]

Post the exact formula you're using. Also, describe how the lookup
table is organized--i.e., is it sorted by the lookup criteria, and, if
so, how is it sorted?

 

[Ron Rosenfeld]

I'm using vlookup in Excel 2003 to return exact matches from another sheet
and it has some error where it returns the #N/A error for a few values, even
though the matching target is present. I don't think it's user error -- I can
open the same file in Open Office Calc and all the values are correctly
returned. Is there any fix for this? I have all the current patches applied.

Bugs of this nature have not been reported that I am aware of. It is likely
that your lookup values and targets are subtly different.

Assuming range_lookup is FALSE, it could be something like a number formatted
as text.

If you post formula, data, and any formulas used to generate that data (and the
precedents), you'll be more likely to obtain a result.
--ron

 

[Ken Wright]

Find an example in your data where you think they are the same but give an
#N/A in the formula, and then in any other cell, just do =Cell1=Cell2 using
your two values. If it says FALSE then no matter what you are seeing, they
are NOT the same.

If the data is text then look at leading/trailing spaces as a root cause,
and if it is numeric then look at decimal precision.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------