Look for multiple strings in a cell

[reddy14]

Hi,

I would like to check if any of 'n' strings in a cell and return "found" if any of the 'n' strings is found in the cell.

Example:

A1 contains - "MAYURI FOODS AND VIDEO REDMOND WA"

A2 should display "found" if A1 contains one of the values "FOOD, APNA, UDUPI"

I tried =IF(COUNTIF(B167,{"*MAYURI*","*APNA*","*UDUPI*"}),"found") . But it did not work.

Can any one give me a solution to my requirement?

Thanks in advance.

[NBVC]

Try:

=IF(SUM(COUNTIF(B167,{"*MAYURI*","*APNA*","*UDUPI*"})),"found","")

[reddy14]

THANKS A LOT NBVC for your quick reply. It worked.

When I use the formula =COUNTIF(B167,{"*MAYURI*","*APNA*","*UDUPI*"})m it is giving me 1 if B167 has "MAYURI FOODS AND VIDEO REDMOND WA"
and it is returning 0 if B167 has "UDUPI CAFE BELLEVUE WA"

Could yo please tell me why it is returning zero in the second case?

Also, could you tell me the reason for using SUM() in the formula?

[NBVC]

Select the cell with your original formula and the go to the Formula tab, and then click Evaluate Formula in the Formula Auditing section. Click the Evaluate formula and you will see that it actually only looks at the first string *MAYURI*". So it counted 1 when Mayuri was within the string.

Now try again with the COUNTIF() wrapped in a SUM()

=SUM(COUNTIF(B167,{"*MAYURI*","*APNA*","*UDUPI*"}))

you will see now, it evaluates each of the values withing the criteria string, then it sums the values... so any sum over 0 is considered "TRUE" for your IF statement.

[reddy14]

Thanks again for detailed explanation. I will keep in mind the tip "evaluate formula" which is very helpful.

I understand that =COUNTIF(B167,{"*MAYURI*","*APNA*","*UDUPI*"}) is equal to =COUNTIF(B167,{"*MAYURI*"})

Now, another question raised in my mind. I would like to know the answer for it to improve my knowledge though it is not in my current requirements.

Question is, "Which function should be used to check if ALL the 3 values(Mayuri, UDUPI, APNA) are part of B167?"

[NBVC]

If your looking for a 1/0 answer if all three are found, then:

=--AND(COUNTIF(I1,"*MAYURI*"),COUNTIF(I1,"*APNA*"),COUNTIF(I1,"*UDUPI*"))

if you use SUM, it could falsely tell you 3, but it could be that one of the words are duplicated and one of them doesn't exist in the string.

in an IF, you would use

=IF(AND(COUNTIF(I1,"*MAYURI*"),COUNTIF(I1,"*APNA*"),COUNTIF(I1,"*UDUPI*")),"Found","")

[reddy14]

Thank you.

[NBVC]

You are welcome.