How do I use a nested IF(AND) in an Excel array formula?
How do I get a nested 'AND' to work inside 'IF' in an array formula?
I reduced my problem to the following example:
Note: the above image has been updated to included the array formula curly braces
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.
The second table shows an array formula that matches the color.
The third table shows an array formula that matches the shape.
On the right is my attempt to use both criteria in an array formula, by combining them with AND.
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!".
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!
Note: 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.
The key to answering these questions is to write something that works as an array formula, 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.
Answer
Try to use next array formula:
=IF(($J$16:$J$22=$L$3)*($K$16:$K$22=$L$4),"MATCH!","-")
here is a link to the test workbook.