title: Excel - INDEX(MATCH) or similar to return an array
body: <p>Supposing the following catalogue of prices:</p>
  
  <pre><code>SHEET 1
  
        A           B
      1 APPLES      1000
      2 FISH        2000
      3 BUTTONS     3000
      4 MINCEPIES   4343
      5 HAMMERS     2343
  </code></pre>
  
  <p>I would like to return an array of values for items of an arbitrary length:</p>
  
  <pre><code>SHEET 2
  
        A
      1 HAMMERS
      2 FISH
      3 BUTTONS
      4 MINCEPIES
      5 APPLES
      6 FISH
  </code></pre>
  
  <p>I need a formula which will return an array of values of column B on Sheet 1 based on the text of column A of Sheet 2 ... so:
  2343;2000;3000;4343;1000;2000</p>
  
  <p>I want to use the array as a component for use in a SUMPRODUCT formula. The closest I have come so far is: </p>
  
  <p><code>{=INDEX(Sheet1!B1:B5,MATCH(Sheet2!A1,Sheet1!A1:A5,0),0)}</code></p>
  
  <p>... however this does not return an array, it just matches the value which corresponds to the top most MATCH column.</p>
  
  <p>Thanks</p>