Search for a set of different strings in a cell & output in one cell

[Mccream1]

Hi everyone,

I am facing a tough one. I have a list (table) with "blacklisted" words in my excel file. I want to search one cell, which is a very long string joined together, for ANY of those words. In case ONE of those words is included in that string, I want a TRUE BOOLEAN statement, else FALSE.

I attached an example excel file. The yellow cell should output a TRUE or FALSE.

It sounds quite simple, yet I am not able to wrap my head around it. Your help is highly appreciated.

Cheers

[bebo021999]

Try in B2:

=OR(ISNUMBER(SEARCH($A$2:$A$4,C2)))

[PaulM100]

Or:

=IF((ISERROR(LOOKUP(1E+100,SEARCH(BlacklistAnchors!$A$2:$A$88,C2),BlacklistAnchors!$A$2:$A$88))),"FALSE","TRUE")

[Mccream1]

Try in B2:

=OR(ISNUMBER(SEARCH($A$2:$A$4,C2)))

This is not working for me. It always returns FALSE.

Also I am looking to check the "BlacklistAnchors" sheet (second one).

[PaulM100]

THe formula in #3 post should do the trick

[Phuocam]

Or try:

=SUMPRODUCT(COUNTIF(C2,"*"&Table7[Blacklist Anchors]&"*"))>0

[Mccream1]

Or:

=IF((ISERROR(LOOKUP(1E+100,SEARCH(BlacklistAnchors!$A$2:$A$88,C2),BlacklistAnchors!$A$2:$A$88))),"FALSE","TRUE")

This one works like a charm. Thank you so much! However, I am not quite sure if I understand why or how it works !

[PaulM100]

Here is an explanation on the Lookup part:http://www.excelforum.com/excel-form...-a-lookup.html
The IF+ISERROR will return the FALSE if no word is found in string and TRUE if one of them is found.

[FlameRetired]

Another way.

Please Login or Register  to view this content.