Text & Cleanup

Excel formula for Check if text contains any word from a list

Flag a cell if it contains any of several keywords at once.

Copy this formula

=IF(SUMPRODUCT(--ISNUMBER(SEARCH(Words, A2)))>0, "Yes", "No")

How the formula works

SEARCH checks each word in the Words range against the cell, ISNUMBER turns hits into TRUE, the double negative makes them 1/0, and SUMPRODUCT totals them — greater than zero means at least one keyword was found.

Examples

=SUMPRODUCT(--ISNUMBER(SEARCH(Banned, A2)))>0→TRUE

TRUE if the text contains any banned word.

=IF(SUMPRODUCT(--ISNUMBER(SEARCH({"refund","cancel"}, A2)))>0, "Review", "")→Review

Lists the words inside the formula instead of in a range.

=SUMPRODUCT(--ISNUMBER(SEARCH(Words, A2)))→2

Returns how many of the listed words appear in the text.

FAQ

How do I require ALL words instead of any?

Compare the count to the list length: …=COUNTA(Words) is TRUE only when every word is found.

Why does it return Yes for every row?

The word list contains a blank cell. SEARCH finds an empty string in any text, so remove blanks from the list.

How do I match whole words only?

Pad both sides with spaces: SEARCH(" "&Words&" ", " "&A2&" "). This stops a short word from matching inside a longer one.

How do I return the word that was found?

In Excel 365 use =TEXTJOIN(", ", TRUE, FILTER(Words, ISNUMBER(SEARCH(Words, A2)), "")).

Related formulas

Return a value if a cell contains text Check if a value exists in a list

Functions used

SUMPRODUCT ISNUMBER SEARCH

Need a formula for your own data?

Describe what you want in plain language and let ExcelGPT write, explain, and verify the formula — free to start, no plugin.

Try ExcelGPT free