# How to check if a cell contains any word from a list in Excel

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").

**Excel:** `=IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$4,A2)))>0,"Yes","No")` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Ticket subject | Escalate? |  | Keywords |
| Refund not received |  |  | refund |
| Change delivery address |  |  | cancel |
| Please CANCEL my order |  |  | broken |
| Login problem after update |  |  |  |
| Item arrived broken |  |  |  |
| Where is my parcel? |  |  |  |
| Cancel and refund please |  |  |  |

Formula in B2, filled to B8: `=IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$4,A2)))>0,"Yes","No")`

Result: Yes; No; Yes; No; Yes; No; Yes

## 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.

## Other ways

- `=IF(OR(ISNUMBER(SEARCH($D$2:$D$4,A2))),"Yes","No")`: 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: 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&" "`.

## 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)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/check-if-cell-contains-any-word-from-a-list