title: How do I create a search over a cell range, of multiple values, returning multiple results (per match) in once cell
body: <p>I want to run a search over a range of cells and check if they contain a combination of specific numbers, even better if taken from the string contained in the cell within the same range. i.e: I want to check if cells B2:B16 contain both 1 and 2.</p>
  
  <p>I then want to take the values from column A, in the rows which matched the search, and return them to a single cell in column C.</p>
  
  <p>In the example shown n, cell C2 lists all the ID numbers from Column A from rows, which correspond to the cells in column B which contain a string with the number "1". (Bonus for the one who also adds an automation on extracting the number from each cell's string).</p>
  
  <p>The job gets trickier when descending down the cells in column B, as the search has to find string with a combination of several numbers (AND rule).
  The only way I managed to get the results in cell C2 is by using a very long combination of:IF(ISNUMBER(SEARCH, which is not practical, as I need to manually adjust it for each cell in Column C.</p>
  
  <p>The formula is as follows:</p>
  
  <pre><code>=IF(ISNUMBER(SEARCH(D2,B3,14)),A3," ")&amp;IF(ISNUMBER(SEARCH(D2,B4,14)),A4," ")&amp;IF(ISNUMBER(SEARCH(D2,B5,14)),A5," ")&amp;IF(ISNUMBER(SEARCH(D2,B6,14)),A6," ")&amp;IF(ISNUMBER(SEARCH(D2,B7,14)),A7," ")&amp;IF(ISNUMBER(SEARCH(D2,B8,14)),A8," ")&amp;IF(ISNUMBER(SEARCH(D2,B9,14)),A9," ")&amp;IF(ISNUMBER(SEARCH(D2,B10,14)),A10," ")&amp;IF(ISNUMBER(SEARCH(D2,B11,14)),A11," ")&amp;IF(ISNUMBER(SEARCH(D2,B12,14)),A12," ")&amp;IF(ISNUMBER(SEARCH(D2,B13,14)),A13," ")&amp;IF(ISNUMBER(SEARCH(D2,B14,14)),A14," ")&amp;IF(ISNUMBER(SEARCH(D2,B15,14)),A15," ")&amp;IF(ISNUMBER(SEARCH(D2,B16,14)),A16," ")
  </code></pre>
  
  <p>Any idea on how I can make this whole operation more efficient?</p>
  
  <p><img src="https://i.stack.imgur.com/8FivN.jpg" alt="enter image description here"></p>