Formula does not capture all values

[rthakur]

Hello!

I use below formula to compare and load a specific message if the value matches or does not match. The formula does not pick up all values correctly. Very few entries are labeled with "not match" incorrectly.

=IF(F2="","",IF(ISNUMBER(MATCH(LEFT(F2,6),'PSCCR Queue - Manager'!$H$4:$H$201,0)),"CA - Natl","Non - CA Natl"))

Thank you for your kind help!

RT

 

[p45cal]

There's only one thing you can do, that is to find an incorrect result, and compare the 6 leftmost characters of the cell in column F with the expected value in column H. I can 99% guarantee there'll be a difference; a leading/trailing space in one of them perhaps (use TRIM), upper/lower case doesn't matter with MATCH, but maybe if the values are pure numbers there may be instances where you're comparing a number with a number represented by text?

 

[rthakur]

Thanks p45cal. Yes, some values are numeric only, while other are alpha-numeric. Your hypothesis is correct.

I am not familar with the TRIM formula. Would you be able to suggest a formula please?

regards,

RT

 

[p45cal]

Help is your best bet.
Where you have
LEFT(F2,6)
use either
LEFT(TRIM(F2),6)
or
TRIM(LEFT(F2,6))

Also, as far as converting numbers to text, you can use
=TEXT(B16,"@")
which i think does nothing to text values.

I'm not going to reiterate the Help file contents.

Where you have
'PSCCR Queue - Manager'!$H$4:$H$201
I don't know if this will work but you could try
TEXT('PSCCR Queue - Manager'!$H$4:$H$201,"@")
instead. Perhaps add TRIM in there too?

I don't know the kinds of data in either what your searching, nor what's being sought, so I can't detail a formula. It's up to you to experiment