How to check if a cell contains any word from a list in Excel
Formula (Excel and Google Sheets)
=IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$4,A2)))>0,"Yes","No")Search for every word in the list at once and count the hits: =IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$4,A2)))>0,"Yes","No").
- Result on the example
- one result per row in
B2, filled down toB8 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in B2, filled down to B8. Change any input and the result updates, or edit the formula to experiment.
How it works
SEARCH with a range looks for each keyword in the subject and returns a position or #VALUE! for each one. ISNUMBER turns that into TRUE or FALSE, the double minus into 1 or 0, and SUMPRODUCT adds them: the number of keywords found. More than 0 means at least one matched. SEARCH ignores case, so "CANCEL" counts.
=IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$4,A2)))>0,"Yes","No")Result"Yes"
$D$2:$D$43 rows × 1 col: "refund", "cancel", "broken"A range: 3 cells in one column.
A2"Refund not received"The value in A2.
SEARCH($D$2:$D$4,A2){3 rows × 1 col} 1, #VALUE!, #VALUE!SEARCH: Position of text, not case-sensitive, allows wildcards.- find_text
$D$2:$D$43 rows × 1 col: "refund", "cancel", "broken" - within_text
A2"Refund not received"
- find_text
ISNUMBER(SEARCH($D$2:$D$4,A2)){3 rows × 1 col} TRUE, FALSE, FALSEISNUMBER: TRUE if the value is a number.- value
SEARCH($D$2:$D$4,A2){3 rows × 1 col} 1, #VALUE!, #VALUE!
- value
-ISNUMBER(SEARCH($D$2:$D$4,A2)){3 rows × 1 col} -1, 0, 0Makes the value negative.
--ISNUMBER(SEARCH($D$2:$D$4,A2)){3 rows × 1 col} 1, 0, 0Makes the value negative.
SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$4,A2)))1SUMPRODUCT: Multiplies arrays row by row and adds the results.- array1
--ISNUMBER(SEARCH($D$2:$D$4,A2)){3 rows × 1 col} 1, 0, 0
- array1
SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$4,A2)))>0TRUEThe > operator tests greater than.
IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$4,A2)))>0,"Yes","No")"Yes"IF: Returns one value if a test is true, another if false.- logical_test
SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$4,A2)))>0TRUE - value_if_true
"Yes" - value_if_false
"No"not used
- logical_test
In Google Sheets
The same formula works in Google Sheets: =IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$4,A2)))>0,"Yes","No"). We ran it in Google Sheets mode on the same data and it returns the same results.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =IF(OR(ISNUMBER(SEARCH($D$2:$D$4,A2))),"Yes","No") | Yes, No, Yes… | Excel 365 evaluates the array inside OR directly. In older versions it needs Ctrl+Shift+Enter, which is why SUMPRODUCT is the classic answer. |
| =IF(REGEXMATCH(A2,"(?i)refund|cancel|broken"),"Yes","No")Google Sheets | Yes, No, Yes… | Google Sheets: one pattern with the words separated by |. (?i) makes it ignore case. |
Common mistakes
- An empty cell in the keyword range matches everything, because every text "contains" an empty string. Keep the range exactly as long as the list.
- Keywords match inside longer words: "cancel" is found in "cancellation". That's usually what you want; for whole words, search for
" "&$D$2:$D$4&" "in" "&A2&" ".
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Check if a cell contains any word from a list
B2, show "Yes" when the subject contains any of the keywords in D2:D4 and "No" otherwise. Fill down to B8.Questions
How do I return which keyword matched?
Look up the first hit: =XLOOKUP(TRUE,ISNUMBER(SEARCH($D$2:$D$4,A2)),$D$2:$D$4,"").
How do I check that the cell contains all the words?
Compare the count with the number of words: =SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$4,A2)))=ROWS($D$2:$D$4).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.