Look for a list of strings in sentence and if found, return a value

Hi guys,

New joiner here, but I have been following the forum for about 2 weeks now.

I am getting a dead end in solving the excel function that would be able to help me in achieving the outcome that I wanted.

It is basically finding for a set of strings in sentence and if found, return a value.

To visualise it:

Sentence (to be scanned)
xxx AAA ppp
yyy AAA
zz BB pp qq
zz CC pp
zz 99 pp
xpo CC xpo

Strings (to look for)
AAA
BB

Result
Found
Found
Found
Not Found
Not Found
Not Found

The column "Result" is the outcome that need to be achieved. For each cell in "Sentence", scan if any of the value in "Strings" exist. If it exist, return Found, else Not Found.

Thank you in advance.

Hi,

Assuming your list of sentences and list of search strings are in A2:A7 and D2:D3 respectively, and that you would like cases such as Sentence: zz AAAA pp; String: AAA to be considered as "Not Found", enter this formula in B2 and copy down as required:

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH("|"&$D$2:$D$3&"|","|"&SUBSTITUTE(A2," ","|")&"|")))),"Found","Not Found")

Regards

with strings starting a1 and list in d1:d2
in b1 filled down

Formula:

Please Login or Register  to view this content.

Not sure I follow. The formula I gave you and copied down matches precisely your desired results as given in your first post.

Regards

yyyxxxxxxxyieuAAAppppo
yyy xxxxxxx yieu AAA ppppo

would both those match aaa?

You're welcome!