How do I get REGEXMATCH to look for multiple terms across a range of cells rather than looking at one cell?

0

Issue

Working in google sheets, I have the following formula:

=if(REGEXMATCH(A1,B1),C1,"")

The idea is that if any part of cell A1 contains the text listed in B1, then the output will be C1, otherwise, the cell is left blank.

My issue is that I need to search A1 not just for text from B1, but for ANY matches from a range of cells (say, B1:B10), and, in the case of a match, return the corresponding value in column C. i.e., if it searches A1 and finds a term from B4, then it needs to return C4 (rather than C1, C2, etc.).

However,

=if(REGEXMATCH(A1,B1:B10),(C1:C10,"") is not correct.

I’ve tried VLOOKUP and INDEX & MATCH to deal with the problem of matching the contents of column B to C, but the issue with those functions is that they don’t search through the entire cell for a single value, they just try to match the entire cell.

Second question: where in the (correct) formula would I put the (?!) argument to denote a case-insensitive search?

Solution

=ARRAYFORMULA(IF(REGEXMATCH(A1:A, B1:B), C1:C, ))

0

=QUERY(ARRAYFORMULA(IF(REGEXMATCH(A1, B1:B), C1:C, )), "where Col1 is not null")

00

=QUERY(ARRAYFORMULA(IF(REGEXMATCH(LOWER(A1), LOWER(B1:B)), C1:C, )), 
 "where Col1 is not NULL")

f

Answered By – player0

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Leave A Reply

Your email address will not be published.

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More