How to check if a cell contains any word from a list
✓ Verified in LibreOffice 25.8.7.3Flag rows whose text includes any of several keywords — banned words, categories, tags to match.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =SUMPRODUCT(--ISNUMBER(SEARCH(D2:D4,A2)))>0 | D2:D4 = the list of words, A2 = the text. Returns TRUE if any word is found (case-insensitive). |
| Google Sheets | =SUMPRODUCT(--ISNUMBER(SEARCH(D2:D4,A2)))>0 | Identical. |
| LibreOffice Calc | =SUMPRODUCT(--ISNUMBER(SEARCH(D2:D4,A2)))>0 | Identical. |
How it works
SEARCH looks for each word from the list inside the cell and returns a position number when found or an error when not; ISNUMBER turns those into TRUE/FALSE, the -- converts to 1/0, and SUMPRODUCT adds them — a total above 0 means at least one word matched. "large red widget" contains "widget" (from the list), so the result is TRUE. SEARCH is case-insensitive; swap it for FIND to match case. To return WHICH word matched, wrap in a lookup; to count HOW MANY matched, drop the >0 and keep the SUMPRODUCT. This is the array-powered version of stacking many ISNUMBER(SEARCH(...)) tests with OR.
Verified, not just documented
We ran =SUMPRODUCT(--ISNUMBER(SEARCH(D2:D4,A2)))>0 in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned True — exactly the expected result. Every formula here is confirmed by actually executing it.