Best explained with an example:

I want to search the blue range and check if any of the cells contain any of the strings in the green range.

Ideally non-case-sensitive, and the search string could appear anywhere within the searched cells.

CodePudding user response：

If...

search range: A1:A10

search key: B1:B3

... then use the following formula

=arrayformula(sum(if(regexmatch(textjoin(",",false,",",A1:A10,","),","&B1:B3&","),1,0)))>0

Feel free to read the documentation of the functions in question.

The basic idea here is that: we want to be able to join the search words into 1 string and apply arrayformula to individual search keys; and then, we want to search whole words.

So how do we easily search whole words? Your search words are divided by cells. So lets put , between them but also wrapping them. Now ","&search_key&"," marks a matched word -- not just a component of a search word.

The rest is doing and operation on array. Google Sheet unfortunately doesn't have functions like any or all. So the most (computationally) efficient thing to do is to use if (in comparison to alternatives like matrix multiplication or filter). The position of arrayformula doesn't matter here so you can just put it outside everything.

CodePudding user response：

Here's another possible solution:

=ARRAYFORMULA(IF(BYROW(A5:C,LAMBDA(r,SUM(LEN(r))))=0,,BYROW(REGEXMATCH(A5:C,"\b"&TEXTJOIN("\b|\b",1,E1:E)&"\b"),LAMBDA(r,SUM(--r)>0))))

Note that this formula is entered once in D5 and it doesn't have to be dragged down.

CodePudding user response：

use:

=INDEX(REGEXMATCH(FLATTEN(QUERY(TRANSPOSE(A5:C7),,9^9)), 
 "(?i)\b"&TEXTJOIN("|", 1, E1:E)&"\b"))