Errors in VLOOKUP

[Amit]

Hi
I've been using vlookup quite frequently
There is a recurring problem that crops in
Sometimes, though the lookup_value is present in the table_array as well as the worksheet where lookup is being used , the result is still #N/
The lookup_value in both cases is from different sources ie the raw data is from an inhouse application software while the 2nd case is plain excel 2000 workshee
Thank
Ami

 

[Markus L]

Sometimes, though the lookup_value is present in the table_array as
well as the worksheet where lookup is being used , the result is
still #N/A

Probably one of them is a number, the other one is formatted as text.
Am having this all the time with data exported from an Access database.
Do this with the "lookup_value" column and the leftmost column of
"table_array":

Data >> Text to Columns... >> Finish.

HTH
Markus

 

[Frank Kabel]

Hi
probably both values are not identical. Some reasons:
- number/text formats mixed
- spaces or invisible characters

Try comparing them manually (e.g. if you think cell A1
mathces cell A3 in your lookup range try:
=A1='lookup_sheet'!A3
This should return TRUE. If this returns FALSE try:
=--A1=--'lookup_sheet'!A3
If this returns TRUE you have a problem with a text format
for a number

or try
=TRIM(A1)=TRIM('lookup_sheet'!A3)
this would indicate spaces in your values

-----Original Message-----
Hi,
I've been using vlookup quite frequently.
There is a recurring problem that crops in.
Sometimes, though the lookup_value is present in the

table_array as well as the worksheet where lookup is being
used , the result is still #N/A

The lookup_value in both cases is from different sources

ie the raw data is from an inhouse application software
while the 2nd case is plain excel 2000 worksheet