This question is based on an someone else's question that I answered. You can find the original question here, if desired. (And, maybe provide a better answer than I have.) However, everything you need to answer this question is right here.
Background
Given the following data in A1:C5
+-------------+--------+----------+
| Invoice Nbr | Type | Status |
+-------------+--------+----------+
| A0001 | Credit | Paid |
| A0002 | Credit | Not Paid |
| B0001 | Debit | Paid |
| B0002 | Debit | Not Paid |
+-------------+--------+----------+
The goal was to find a particular value in the table using an array formula that evaluated two columns. That is, give me the first Invoice (Column A) where Type is Debit and Status is Not Paid.
My first answer was an attempt to fix what I thought to be a problem with the OP's conditional. I put an AND wrapper around the two conditions as follows:
{=INDEX($A$2:$A$5,
SMALL(IF(AND($B$2:$B$5 = "Debit",
$C$2:$C$5 = "Not Paid"),
ROW($A$2:$A$5)-ROW($A$2)+1),
1)
)}
However, that didn't work.
I ended up suggesting this answer, because it actually works:
{=INDEX($A$2:$A$5,
SMALL(IF($B$2:$B$5 & $C$2:$C$5 = "DebitNot Paid",
ROW($A$2:$A$5)-ROW($A$2)+1),
1)
)}
My Question
Array formulae in Excel are sometimes so much voodoo to me. It seems like either one should provide the result B0002. In fact, only the second formula gives the desired result. What principle or evaluation process is Excel (2013) following that makes this so? Or, in other words, what am I not understanding about how Excel manages array formulae?
