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

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" wrote:

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

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




"Ron Coderre" wrote in message
...
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" wrote:

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

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

"Grant" wrote in message ...
|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
|
|