title: Excel Array formula IF(multiple criteria)
body: <p>In my spreadsheet, comparing headphones, our instruction is basically to use as many different formulas as possible. </p>
  
  <p>So as part of the spreadsheet, I would like to be able to show the most expensive headphones <em>per manufacturer</em>, cheapest per manufacturer, best rated per manufacturer etc...
  So far I have been able to get some mostly working array formulas.</p>
  
  <p>For example, this formula works to get the <em>model</em> of the manufacturer "Sennheiser" with the highest price:</p>
  
  <pre><code>=INDEX($Data.$B$5:$L$32,SMALL(IF($Data.$E$5:$E$32 = $Sheet2.D17, ROW($Data.$B$5:$E$32) - ROW($Data.$B$5) + 1, ROW($Data.$E$32) + 1), 1), 2)
  </code></pre>
  
  <p>Column E is the Price column and D17 is a pre-calculated Max price for "sennheiser"</p>
  
  <p>This works fine, until you get 2 headphones with the same price / rating or whatever. THen it starts returning the wrong values.</p>
  
  <p>So I tried various solutions that I found on the interwebs, like </p>
  
  <pre><code>AND(condition1, condition2) 
  condition1 * AND(cndition2)
  condition1 * condition2
  </code></pre>
  
  <p>but none of this seems to work with an array formula for some reason. I get #N/A or #VALUE and various other errors.
  SO basically I would like to know how to modify my formula, or even a completely new formula, to check for lowest price AND the correct manufacturer.</p>
  
  <p>I hope my question is clear, so I have uploaded the spreadsheet to get some idea of what I am talking about.</p>
  
  <p><a href="http://dl.dropbox.com/u/18816338/Stats%20Analysis%20%20%281%29.xlsm" rel="nofollow">http://dl.dropbox.com/u/18816338/Stats%20Analysis%20%20%281%29.xlsm</a></p>
  
  <p>Thanks in advance</p>