3

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?

player0
  • 124,011
  • 12
  • 67
  • 124
doctorw0rm
  • 133
  • 1
  • 5

1 Answers1

3
=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

player0
  • 124,011
  • 12
  • 67
  • 124
  • 2
    This does the trick if you're only searching A1 for "hel". What if you want EVERY cell in column A for "hel" and "jk"? For example, if you put "hello" in A2, it won't display "ooo" in C2 (but I would like it to). – doctorw0rm Mar 02 '19 at 21:50
  • Perfect. One side question, edited into original post: where in the argument do I put (?!) to denote a case-insensitive search? – doctorw0rm Mar 02 '19 at 22:13