title: VLOOKUP return an array of all matches in Excel
body: <p>I need to return an array of all matches for a VLOOKUP then return a value based on what's in the array. The data looks like this
      <pre>
  product_style        color         in_stock
  11111                Green          0
  11111                Blue           1
  22222                Green          1
  22222                Blue           0
  22222                Red            0
  33333                Green          1
      </pre>
  I need a vlookup to find all products based on the style, and find out if it has any colors in stock then return 0 or 1. Its easy to find the first value</p>
  
  <pre><code>=IF(VLOOKUP(A2,A2:C7,3,0)=1,1,0)
  </code></pre>
  
  <p>The problem is that it will just grab the first occurrence and return that value.</p>
  
  <p>A possible solution is to add them all up, since I am using 0 or 1 for in_stock, so</p>
  
  <pre><code>=IF(VLOOKUP TOTAL &gt; 0,1,0)
  </code></pre>
  
  <p>That would work if I could get the VLOOKUP TOTAL</p>
  
  <p>The only restriction is that the resulting array must all be in a single cell, I've seen this answer, but the resulting array shows up in multiple cells</p>
  
  <p><a href="http://office.microsoft.com/en-us/excel-help/how-to-look-up-a-value-in-a-list-and-return-multiple-corresponding-values-HA001226038.aspx" rel="nofollow">http://office.microsoft.com/en-us/excel-help/how-to-look-up-a-value-in-a-list-and-return-multiple-corresponding-values-HA001226038.aspx</a></p>