fixing conditional array formulae in Excel

I know what you’re thinking- how can you improve on conditional array formulas? It’s like a steroid-fueled, adrenaline-charged thrill ride of Excel data parsing! But it has given me fits when I try to nest multiple conditions across different arrays in a single-cell formula. Let me explain.

First, the value of array formulas is that they can parse entire arrays, like a whole column or more of data. Second, they return arrays as output instead of a single cell’s value. This is huge when sifting through massive amounts of data, looking for specific instances that meet discrete and unique criteria for evaluation.

Conditional statements (IF formulas) are incredibly useful in these cases but are difficult to use in single-cell array formulas. This is because they must evaluate to a single value, which by default must be true or false. It defeats the purpose of parsing out items that meet the criteria. For example-

This formula seeks to find entries from a large dataset for only those subjects who are also present in a smaller subset.  So evaluation proceeds as such-

And then-

And here we see a single instance of TRUE where the subject matched!  So you might expect the end result to be “condition met”, right? Wrong-

This is because an IF condition in Excel must have a single logical outcome. Any one FALSE result in an array output will cause the entire array to collapse to a FALSE result. After all, in a single-cell array formula, there can be only 1 answer…

Now it’s quite possible to solve this by pointing the array formula into multiple cells, a vertical range with at least as many cells as the resulting array may possess elements. In this case, it’s not bad as the match is performed against an array of only 13 items. But in another instance, it could be hundreds or more in my line of work. There has to be a better way. And there is-

This formula converts the resulting array of the IF condition to a 1 if TRUE and a 0 if FALSE. This is incredibly useful since numbers are far easier to work with than TRUE or FALSE logical results-

And then-

And then-

And here is revealed the utility. The output array of 13 items has been converted to numbers, and a second condition seeks to determine if there is a single, solitary match-

And there is-

And my job just got a lot easier for the other 2239 rows-

This example shows the true power of using array formulas in Excel, assuming you can get them to work, and that your machine has sufficient power to chug through them without crashing. But when triple conditions nested into array formulas do all the heavy lifting, it can be a beautiful thing!