Spreadsheet (GSheet) formula to check content in a column-CodePudding

I have rows with sentences. I need to check if at least one word of each sentence is in the column "Word Check" indicating so in each row. I tried with different formulas with no success. Can you help me please? Thank you.

CodePudding user response：

I found how to: =IF(REGEXMATCH(H18,TEXTJOIN("|",1,$J$17:J26)),"Yes","")

CodePudding user response：

With the rest of the 'Contains' column cleared, you can use this to check any phrase in A2:A against the word list in C2:C. There's only one L in 'Colorful', though, so if there are spelling errors in the Word Check list, they may not be checked correctly against the phrases.

=ARRAYFORMULA(
  IF(ISBLANK(A2:A),,
   IF(
    REGEXMATCH(
     A2:A,
     "(?i)"&TEXTJOIN("|",TRUE,C2:C)),
    "Yes",)))