VLOOKUP not working???

[Gary]

Hi All....

Okay.....I dont know WHY this formula is giving me #N/A.

=VLOOKUP(A1,Sheet2!$T$2:$U$259,2,FALSE)

I do not see anything wrong with this formula....what could cause this???

Thanks in advance
Gary

 

[Niek Otten]

Hi Gary,

The formula looks OK, but A1 seems not to be present in your list.
Maybe one is text and the other a number; you don't see the difference, but Excel does. Use the ISNUMBER and/or the ISTEXT
function to check.
Use the VALUE or the TEXT function to convert one type to the other.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi All....
|
| Okay.....I dont know WHY this formula is giving me #N/A.
|
| =VLOOKUP(A1,Sheet2!$T$2:$U$259,2,FALSE)
|
| I do not see anything wrong with this formula....what could cause this???
|
| Thanks in advance
| Gary
|
|

 

[Alan]

There's nothing wrong with the formula, it must be either in the lookup
range or what is in A1. Are the values you're searching the results of
formulas? If so it may well be a rounding issue,
Regards,
Alan.

 

[Gary]

There are no formulas....they are simply names and i need to lookup the
process they are working for. thats it......and its not that A1 is not
there..it is there. even if i drag it down...it shows NA in all the cells...

and theres one new thing i just discovered. I am trying to lookup A1...the
name in A1 was copied from an email body.....now if i type the same name in
A1, it gives me the result.....but i can not type it....tahts why i ma using
VLOOKUP. its weird...am seeing it for the first time.
please help

 

[Niek Otten]

Hi Gary,

There may be invisible characters or spaces in your search argument. Use the CLEAN() and/or the TRIM() function and if that
doesn't help, use David McRitchies TrimAll function:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| There are no formulas....they are simply names and i need to lookup the
| process they are working for. thats it......and its not that A1 is not
| there..it is there. even if i drag it down...it shows NA in all the cells...
|
| and theres one new thing i just discovered. I am trying to lookup A1...the
| name in A1 was copied from an email body.....now if i type the same name in
| A1, it gives me the result.....but i can not type it....tahts why i ma using
| VLOOKUP. its weird...am seeing it for the first time.
| please help
|
| | > There's nothing wrong with the formula, it must be either in the lookup
| > range or what is in A1. Are the values you're searching the results of
| > formulas? If so it may well be a rounding issue,
| > Regards,
| > Alan.
| > | >> Hi All....
| >>
| >> Okay.....I dont know WHY this formula is giving me #N/A.
| >>
| >> =VLOOKUP(A1,Sheet2!$T$2:$U$259,2,FALSE)
| >>
| >> I do not see anything wrong with this formula....what could cause this???
| >>
| >> Thanks in advance
| >> Gary
| >>
| >
|
|

 

[Gary]

Hey Niek,

Whats the syntax for clean or trim? is it =CLEAN(A1) OR =TRIM(A1)? if yes,
its not working.

 

[Alan]

If this is the problem you would use
=VLOOKUP(CLEAN(A1),Sheet2!$T$2:$U$259,2,FALSE)
or
=VLOOKUP(TRIM(A1),Sheet2!$T$2:$U$259,2,FALSE)
You say the data comes from an email, there is a space used on web sites
that isn't the normal ASCII32 space that XL recognises, its ASCII160. TRIM
or CLEAN wont remove these.
In a cell that contains some of the data, say A1, enter =CODE(A1) which will
give you the ASCII number of the first character in the cell. If it's 160
then you have the dreaded rogue space that XL doesn't recognise.
To get rid of it, highlight the whole range that the copied or imported data
is in, go 'Edit' > 'Replace' > 'Find What' - hold down the Alt key and type
on the numeric keypad 0160, (nothing will be displayed in the dialogue box),
'Replace With' > leave blank, hit 'Replace All'
If it says that any replacements have been made then that was the problem,
Regards,
Alan.

 

[Alan]

Try this anyway, the space may not necessarily be the first character it's
just occurred to me, although it usually is. Finding and replacing it will
do no harm even if it does no good. This same or similar thing happened to
me a long time ago in a project I was involved in and it drove me mad until
it was resolved, I know how you feel!
Regards,
Alan.

To get rid of it, highlight the whole range that the copied or imported data
is in, go 'Edit' > 'Replace' > 'Find What' - hold down the Alt key and type
on the numeric keypad 0160, (nothing will be displayed in the dialogue box),
'Replace With' > leave blank, hit 'Replace All'
If it says that any replacements have been made then that was the problem,