Spreadsheet Practice

How to check if a cell contains one of several words in Google Sheets

Google Sheets formula

=REGEXMATCH(A2,"(?i)"&TEXTJOIN("|",TRUE,$D$2:$D$4))

Join the words into one pattern with | and test it: =REGEXMATCH(A2,"(?i)"&TEXTJOIN("|",TRUE,$D$2:$D$4)).

Result on the example
one result per row in B2, filled down to B7
Excel
See below

See it work

The formula is already in B2, filled down to B7. Change any input and the result updates, or edit the formula to experiment.

How it works

TEXTJOIN builds the pattern refund|cancel|broken, where | means "or". The (?i) at the start makes the match ignore case, so "CANCEL" counts. Because the words come from cells, you can add a keyword without touching the formula.

=REGEXMATCH(A2,"(?i)"&TEXTJOIN("|",TRUE,$D$2:$D$4))

ResultTRUE

  1. A2"Refund not received"

    The value in A2.

  2. $D$2:$D$43 rows × 1 col: "refund", "cancel", "broken"

    A range: 3 cells in one column.

  3. TEXTJOIN("|",TRUE,$D$2:$D$4)"refund|cancel|broken"

    TEXTJOIN: Joins text with a delimiter between items.

    • delimiter"|"
    • ignore_emptyTRUE
    • text1$D$2:$D$43 rows × 1 col: "refund", "cancel", "broken"
  4. "(?i)"&TEXTJOIN("|",TRUE,$D$2:$D$4)"(?i)refund|cancel|broken"

    The & operator joins as text.

  5. REGEXMATCH(A2,"(?i)"&TEXTJOIN("|",TRUE,$D$2:$D$4))TRUE

    REGEXMATCH: TRUE if the text contains a match for the pattern.

    • textA2"Refund not received"
    • regular_expression"(?i)"&TEXTJOIN("|",TRUE,$D$2:$D$4)"(?i)refund|cancel|broken"

In Excel

In Excel 365 use REGEXTEST with 1 as the case argument: =REGEXTEST(A2,TEXTJOIN("|",TRUE,$D$2:$D$4),1). In any version, =SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$4,A2)))>0 does the same.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$4,A2)))>0TRUE, FALSE, TRUE…No regular expressions: SEARCH looks for each keyword and SUMPRODUCT counts the hits. It works in Excel too.

Common mistakes

  • Characters with a meaning in patterns, like . + ( ) ?, must be escaped with a backslash in the keyword list, or they match the wrong thing.
  • An empty keyword cell inside the range would add an empty alternative that matches everything. TEXTJOIN with TRUE skips blanks, so keep that argument.
  • Without (?i) the match is case-sensitive, so "Please CANCEL my order" wouldn't match "cancel" in the list.

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 one of several words

In B2, return TRUE when the subject contains any of the keywords in D2:D4, ignoring case. Fill down to B7.

Questions

How do I match whole words only?

Add word boundaries: =REGEXMATCH(A2,"(?i)\b("&TEXTJOIN("|",TRUE,$D$2:$D$4)&")\b").

How do I return the word that matched?

Use REGEXEXTRACT with the same pattern: =REGEXEXTRACT(A2,"(?i)"&TEXTJOIN("|",TRUE,$D$2:$D$4)).

How do I check a whole column with one formula?

Wrap it in ARRAYFORMULA: =ARRAYFORMULA(IF(A2:A7="","",REGEXMATCH(A2:A7,"(?i)"&TEXTJOIN("|",TRUE,$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.