Formating not recognised in lookup formula

[Guest]

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.

 

[Guest]

=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.

 

[Guest]

Thanks for that.

--
Jim

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.