title: How do I use a nested IF(AND) in an Excel array formula?
body: <p>How do I get a nested 'AND' to work inside 'IF' in an array formula?</p>
  
  <p>I reduced my problem to the following example:
  <img src="https://i.stack.imgur.com/NiFMw.png" alt="Excel spreadsheet showing array formula with nested AND">
  <em>Note: the above image has been updated to included the array formula curly braces</em></p>
  
  <p>At the top right, we have the search criteria in L3 ("color") and L4 ("shape"). At the left, column D contains working match formulas for both color and shape in the list of items. The first table shows the match formula working properly without using an array formula.</p>
  
  <p>The second table shows an array formula that matches the color.</p>
  
  <p>The third table shows an array formula that matches the shape.</p>
  
  <p>On the right is my attempt to use both criteria in an array formula, by combining them with AND.</p>
  
  <p>IF the value in the color column matches the color criteria (L3) and the value in the shape column matches the shape criteria (L4), then I want to see "MATCH!".</p>
  
  <p>I did find a workaround: concatenate the values and criteria, and then match them inside a single IF. I feel like there should be a Better Way... like if AND worked as expected!</p>
  
  <p><strong>Note:</strong>
  Many of the answers below work correctly but not as array formulas, which is specifically what this question is about. I looked at my original question and realized I forgot to show the curly braces in the array formula examples. I have fixed the image to show them. Sorry for the confusion.</p>
  
  <p>The key to answering these questions is to write something that works as an <a href="http://office.microsoft.com/en-us/excel-help/create-an-array-formula-HP005200020.aspx" rel="noreferrer">array formula</a>, which is entered by pressing CTRL+SHIFT+ENTER after typing the formula into a cell. Excel will automaically add the curly braces to indicate that it's an array formula.</p>