Errors in VLOOKUP

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Amit said:
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
Click to expand...

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
 
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
Click to expand...
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
Click to expand...
ie the raw data is from an inhouse application software
while the 2nd case is plain excel 2000 worksheet
 
Back
Top