CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
d0g
d0g

Reputation: 1399

In Google Sheets, how can I test if a range of cells contains any of the text in another range?

Best explained with an example:

enter image description here

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.

Upvotes: 1

Views: 2632

Answers (4)

Ping
Ping

Reputation: 911

result

With the recently added new functions, things can be done as easy as this. The reference range A5:C7 and E1:E3 can be changed to match your needs.

=BYROW(A5:C7,LAMBDA(ROW,REGEXMATCH(JOIN(" ",ROW),JOIN("|",$E$1:$E$3))))

To make it a 'non-case-sensitive' search, you can add UPPER() to both of the reference range.

Since UPPER() itself is not an ArrayFormula, you'll have to wrap the whole thing with ArrayFormula(), so the outcome will look like this:

=ArrayFormula(BYROW(UPPER(A5:C7),LAMBDA(ROW,REGEXMATCH(JOIN(" ",ROW),JOIN("|",UPPER($E$1:$E$3))))))

Just found a problem, that if the green range contains empty cells, it may ruin the result, to get rid of this problem, I added QUERY() to the ref. range of green area like this:

=ArrayFormula(BYROW(UPPER(A5:C7),LAMBDA(ROW,REGEXMATCH(JOIN(" ",ROW),JOIN("|",UPPER(QUERY({E1:E3},"WHERE Col1 IS NOT NULL")))))))

Or, we can include the 'non-case-sensitive' argument into regex2 like this:

=BYROW(A5:C7,LAMBDA(ROW,REGEXMATCH(JOIN(" ",ROW),"(?i)"&JOIN("|",QUERY({E1:E3},"WHERE Col1 IS NOT NULL")))))

Upvotes: 1

player0
player0

Reputation: 1

use:

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

enter image description here

Upvotes: 0

z..
z..

Reputation: 12923

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

enter image description here

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

Upvotes: 2

Argyll
Argyll

Reputation: 9875

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.

Upvotes: 1

Related Questions