vLookup Help

[Grant]

I am sure that I read the solution to this problem b4 but can not locate the
answer. My example is my Lookup_value is 24208853. This number apperars in
the Table_array but I get #N/A returned. The reason is one is formatted as
general and the other is formatted as text. How can I get around this? I do
not have the option of getting specifying the format for the data.

T.I.A.

Grant

 

[Guest]

With
A1: (the lookup value)
and
D1:E10 (the lookup table)

Try this:

If the LookUp value is TEXT
and the table values are NUMERIC:
=VLOOKUP(--A1,D1:E10,1,0)

Otherwise,
If the LookUp value is NUMERIC
and the table values are TEXT:
=VLOOKUP(""&A1,D1:E10,1,0)

Does that help?
***********
Regards,
Ron

XL2002, WinXP

 

[Grant]

This works!
Thanks

Otherwise,
If the LookUp value is NUMERIC
and the table values are TEXT:
=VLOOKUP(""&A1,D1:E10,1,0)

 

[Niek Otten]

Hi Grant,

Unfortunately you don't tell is what "one" is and what "the other".
Let's assume it is the search key (the 1st argument to the Lookup function) which is a number, and the table keys are text.
Replace Your Search Key with:
TEXT(YourSearchKey,"#")

BTW look at the more uptodate versions of this function: VLOOKUP() and, to a lesser extent, HLOOKUP()

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I am sure that I read the solution to this problem b4 but can not locate the
| answer. My example is my Lookup_value is 24208853. This number apperars in
| the Table_array but I get #N/A returned. The reason is one is formatted as
| general and the other is formatted as text. How can I get around this? I do
| not have the option of getting specifying the format for the data.
|
| T.I.A.
|
| Grant
|
|