title: Using VLOOKUP or MATCH for cells that contain comma separated values
body: <p>I am trying to use either <code>VLOOKUP</code> or a combination of <code>INDEX</code> and <code>MATCH</code> for a small project I am working on in Google Sheets, but I am running into trouble when a cell contains comma-separated values.  I have provided an example below:</p>
  
  <p><a href="https://i.stack.imgur.com/Pcqmg.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Pcqmg.png" alt="Example"></a></p>
  
  <p><strong>Column A</strong> and <strong>Column B</strong> make up the search range, and <strong>Column C</strong> contains the search keys.  <strong>Column D</strong> uses the <code>VLOOKUP</code> method, and <strong>Column E</strong> uses the <code>INDEX</code> and <code>MATCH</code> method.  The formulas for the cells in the first row are as follows:</p>
  
  <pre><code>=VLOOKUP("*"&amp;$C1&amp;"*", A$1:B$4, 2, FALSE)
  </code></pre>
  
  <hr>
  
  <pre><code>=INDEX($B$1:$B$4, MATCH("*"&amp;$C1&amp;"*", $A$1:$A$4, 0))
  </code></pre>
  
  <p><strong>Columns D</strong> and <strong>E</strong> should spell out "SUCCESS" vertically if everything works correctly, but obviously, there are some issues.  When I originally started looking for help, I found the method of appending a wildcard character (*) to the beginning and end of the search key in order to disregard text before or after the value I am searching for.  This works except when the criteria for one search key can match a different search key, for example, when I am searching in <strong>Row 1</strong> for "Pg 3" but "Pg 32" and "Pg 33" also exist.  The problem only seems to occur when the value I am searching for is not in a cell by itself AND when it is preceded by a "matching" value in an earlier row (e.g. "Pg 33" matching the "Pg 3" key in <strong>Row 1</strong> and "Pg 64" matching the "Pg 6" key in <strong>Row 7</strong>).</p>
  
  <p>Unfortunately, in the project I am working on, I will not be able to sort the range into alphanumeric order, so any help towards a workaround is appreciated, preferably just using formulas and not delving into Google Apps Script.  Hopefully, my explanation is clear enough, and I have no preference whether I use <code>VLOOKUP</code> or <code>MATCH</code>.</p>