Return yes if text contains any word from the list-CodePudding

I have a set of random texts (column A) and a list of course words (column B). The goal is to determine for each text from A, if it contains any of the course words from column B. Both texts and course words are in lower register.

Tried this formula, but it didn't work =count(SEARCH(D1:D7000,A$1:A$14))

CodePudding user response：

use:

=INDEX(IF(REGEXMATCH(""&LOWER(A2:A); TEXTJOIN("|"; 1; LOWER(B2:B))); "yes"; ))

CodePudding user response：

You can use the COUNTIF function that counts cells that match certain criteria and returns a count of occurrences. If no cells meet the criteria, COUNTIF returns zero.

=IF(COUNTIF(A$1:A$14,"*" & D1:D7000 & "*"),"Yes","No")