SEARCH - False Match

[gldurand]

Hi Experts,

I am using the following formula to find multiple values in a cell.

=SUMPRODUCT(--ISNUMBER(SEARCH({"#4","#5","#13","#44"},Y2)))>0

The problem is I am getting a False Match because it is finding TRUE values like #54, #55, #130 etc.

Is there a way to FIND the true value of what i am looking for

Any help would be great assistance

thanks Again for bailing me out....

 

[Eric W]

How are they separated in the cell? Space, comma, or just the # sign? If it's a comma for example, you could try:

=SUMPRODUCT(--ISNUMBER(SEARCH({"#4,","#5,","#13,","#44,"},Y2&",")))>0

 

[gldurand]

The cells include all sorts of values... but usually separated by semi-colon

POLYPE;#90;#ADÉNOME;#4;#REFLUX GASTRO-OESOPHAGIEN;#100

Your suggestion seems to work, but when the last value is #53, it tells me TRUE, which is not.

 

[Eric W]

You're using the semi-colon in the formula? I'm not sure why the #53 would be a problem. Can you post your formula, as well as the data in the cell that produces the incorrect result?