Return yes if text contains any word from the list

user_15450993 0
Viewed 132

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

google-sheets-formula google-sheets if-statement excel regex
2 Answers

use:

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

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")
user_117985720
Related