Formating not recognised in lookup formula
 

I have a formula that uses index and match to look up values in two lists.
The cells I want to match are created from 2 other cells thus:
=TEXT(F12,"")&"P-"&TEXT(N12,"## ") where F12 is text and N12 a Number the
result is AEEHP-1056.

The result is correct. However, when I use the index/match formula to
compare AEEHP-1056 in another list (created the same way-but data sources
different) I get the result #N/A.

If I overwrite AEEHP-1056 manually into the cell (ie; replace the formula
that created it), the look up works.

I've tried formating as text, and converting text to column whithout any
change, and pasting special values only, and nothing works.

I have several hundred rows to lookup and retypeing each combined
description into each cell is what I'm trying to avoid.

This is driving me nuts. Any help is gratefully appreciated.
--
Jim

Formating not recognised in lookup formula
 

=TEXT(F12,"")&"P-"&TEXT(N12,"## ")
Does your second text function really have that tailing space after the ##?
If so, the result has a trailing space as well, which would cause the match
to fail.


"Jim G" wrote:

I have a formula that uses index and match to look up values in two lists.
The cells I want to match are created from 2 other cells thus:
=TEXT(F12,"")&"P-"&TEXT(N12,"## ") where F12 is text and N12 a Number the
result is AEEHP-1056.

The result is correct. However, when I use the index/match formula to
compare AEEHP-1056 in another list (created the same way-but data sources
different) I get the result #N/A.

If I overwrite AEEHP-1056 manually into the cell (ie; replace the formula
that created it), the look up works.

I've tried formating as text, and converting text to column whithout any
change, and pasting special values only, and nothing works.

I have several hundred rows to lookup and retypeing each combined
description into each cell is what I'm trying to avoid.

This is driving me nuts. Any help is gratefully appreciated.
--
Jim

Formating not recognised in lookup formula
 

Thanks for that.

--
Jim


"bpeltzer" wrote:

=TEXT(F12,"")&"P-"&TEXT(N12,"## ")
Does your second text function really have that tailing space after the ##?
If so, the result has a trailing space as well, which would cause the match
to fail.


"Jim G" wrote:

I have a formula that uses index and match to look up values in two lists.
The cells I want to match are created from 2 other cells thus:
=TEXT(F12,"")&"P-"&TEXT(N12,"## ") where F12 is text and N12 a Number the
result is AEEHP-1056.

The result is correct. However, when I use the index/match formula to
compare AEEHP-1056 in another list (created the same way-but data sources
different) I get the result #N/A.

If I overwrite AEEHP-1056 manually into the cell (ie; replace the formula
that created it), the look up works.

I've tried formating as text, and converting text to column whithout any
change, and pasting special values only, and nothing works.

I have several hundred rows to lookup and retypeing each combined
description into each cell is what I'm trying to avoid.

This is driving me nuts. Any help is gratefully appreciated.
--
Jim