title: How do I get REGEXMATCH to look for multiple terms across a range of cells rather than looking at one cell?
body: <p>Working in google sheets, I have the following formula:</p>
  
  <pre><code>=if(REGEXMATCH(A1,B1),C1,"")
  </code></pre>
  
  <p>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.</p>
  
  <p>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.).</p>
  
  <p>However, </p>
  
  <pre><code>=if(REGEXMATCH(A1,B1:B10),(C1:C10,"") is not correct.
  </code></pre>
  
  <p>I've tried <code>VLOOKUP</code> and <code>INDEX</code> &amp; <code>MATCH</code> 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.</p>
  
  <p>Second question: where in the (correct) formula would I put the (?!) argument to denote a case-insensitive search?</p>