| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| lookup with a formula | Excel Worksheet Functions | |||
| Strange Formating problem with text and formula in same cell | Excel Worksheet Functions | |||
| Lookup Formula - but have a formula if it can't find/match a value | Excel Worksheet Functions | |||
| Copy cell formating as part of a formula | Excel Worksheet Functions | |||
| How do I have a formula lookup a sheetname? | Excel Worksheet Functions | |||
Linear Mode
