title: How to make an IF statement remain true throughout an INDEX MATCH in Excel 2010+?
body: <p>I am trying to use an INDEX MATCH to return the optimal choice from a list of items associated to values that change based on various criteria. My current sheet setup and formula is this:</p>
  
  <pre><code>A     B    C
  Item1 Bad  27
  Item2 Good 15
  Item3 Good 27
  Item4 Bad  44
  </code></pre>
  
  <p>Column A is a named range <code>Item</code>.<br>
  Column B is a named range <code>ItemType</code>.<br>
  Column C is a named range <code>ItemValue</code></p>
  
  <pre><code>=INDEX(INDIRECT(SUBSTITUTE('Sheet'!B3," ","")),MATCH(MAX(IF(INDIRECT(SUBSTITUTE('Sheet'!B3," ","")&amp;"Type")="Good",INDIRECT(SUBSTITUTE('Sheet'!B3," ","")&amp;"Value"))),INDIRECT(SUBSTITUTE('Sheet'!B3," ","")&amp;"Value"),0))
  </code></pre>
  
  <p>(entered as an array formula)<br>
  In its current iteration, this will return <code>Item1</code>. I would like it to return <code>Item3</code>.</p>
  
  <p>In this example, <code>'Sheet'!B3</code> contains the category to search (in this example <code>Item</code>). I use a variety of named ranges based on the data in that cell and suffixes with the use of INDIRECT/SUBSTITUTE. My current formula finds the highest value that also is <code>Good</code>, but then returns the first value that matches it, regardless of it being <code>Good</code> or <code>Bad</code>. I have tried adding an additional IF statement at different points of the formula but the cell would either return <code>FALSE</code> or a formula syntax error. How can I maintain my <code>="Good"</code> IF statement throughout the formula?  </p>
  
  <p>Thank you!</p>