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

## 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, ))
``````

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

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