title: Excel: Check if cell string value exists in column, and get all cell references to that string
body: <p>I suspect this may be a job for VBA, which is beyond my abilities. But here's the scenario:</p>
  
  <p>Column A in Sheet 1 (CAS1) contains x rows of text values</p>
  
  <p>Column A in Sheet 2 (CAS2) contains x rows of text values</p>
  
  <p>Part A - For each row value in CAS1, I need to know if the string is contained in any of the cells in CAS2. Not exact match, the string can be only part of the searched cells.</p>
  
  <p>Part B - I need to know the cell value of each cell in CAS2 that contains the CAS1 value (if they do exist, they can be listed in the cells adjacent to the cell being searched in CAS1).</p>
  
  <p>I've tried the following to attempt Part A, all to no avail:</p>
  
  <pre><code>vlookup(A1,sheet2!A:A,1,false)
  NOT(ISNA(MATCH(A1,sheet2!A:A,0)))
  ISNUMBER(MATCH(A1,sheet2!A:A,0))
  COUNTIF(sheet2!A:A,A1)&gt;0 
  IF(ISERROR(MATCH(A1,sheet2!A:A, 0)), "No Match", "Match")
  </code></pre>
  
  <p>I know some of the cell values in CAS2 contain the cell values in CAS1, so I don't know why they return false or No Match. I suspect it may be down to the nature of the text content. So here's some sample data:</p>
  
  <p><strong>CAS1</strong></p>
  
  <pre><code>LQ056
  RV007H
  RV008
  RV009H
  TSN304
  TSN305
  </code></pre>
  
  <p><strong>CAS2</strong></p>
  
  <pre><code>RV009-satin-nickel-CO.jpg
  STR314.jpg
  STR315.jpg
  HCY001.jpg
  RV008-oval-rad-CO.jpg
  HCY001-BRAC006.jpg
  </code></pre>
  
  <p>Any help would be appreciated.</p>