LOOKUP problem

  • Thread starter Thread starter gerard
  • Start date Start date
G

gerard

I need to use the HLOOKUP function, but it returns a #NA.

The situation is as follows:

Although the Lookup_value is virtual identical to a value
in the Table_array, it nevertheless returns a #NA.

The Lookup_value and the Table_array values are copied
from different sheets. This might seem as irrelevant info,
but somehow the format of the two values are different,
and it might be due to sheet format differences.

It does not change the situation when I copy / paste the
cell formats from Lookup_value to Table_array values.

Does anybody know which formats play a role in LOOKUP
functions others than the obvious ones?
 
gerard said:
I need to use the HLOOKUP function, but it returns a #NA.

The situation is as follows:

Although the Lookup_value is virtual identical to a value
in the Table_array, it nevertheless returns a #NA.

The Lookup_value and the Table_array values are copied
from different sheets. This might seem as irrelevant info,
but somehow the format of the two values are different,
and it might be due to sheet format differences.

It does not change the situation when I copy / paste the
cell formats from Lookup_value to Table_array values.

Does anybody know which formats play a role in LOOKUP
functions others than the obvious ones?
Click to expand...

The important thing is whether the lookup value and table array values are
text or not. Lookups fail if one, but not the other, is text. What's more,
if you have a text value in a cell, simply formatting the cell as number
will not necessarily change it from being text. You also have to either
reenter the cell contents, or copy a blank cell that is not formatted as
text, select the cell(s) you want to change from text and use Edit > Paste
Special > Add.

You can always use a formula such as
=ISTEXT(A1)
to see whether or not another formula will treat a cell (however it is
formatted) as text.
 
-----Original Message-----


The important thing is whether the lookup value and table array values are
text or not. Lookups fail if one, but not the other, is text. What's more,
if you have a text value in a cell, simply formatting the cell as number
will not necessarily change it from being text. You also have to either
reenter the cell contents, or copy a blank cell that is not formatted as
text, select the cell(s) you want to change from text and use Edit > Paste
Special > Add.

You can always use a formula such as
=ISTEXT(A1)
to see whether or not another formula will treat a cell (however it is
formatted) as text.


.
Click to expand...

Thanks Paul,

I found a solution!
The issue is not one of text or number, both are number
format, but it is the format of the values in which it is
stored by Excel, and not how it is displayed.

So, if I use the ROUND function to round the values to
equal digits, then lookup works again.
 
Back
Top