I was trying to answer this StackOverflow question and stumbled on a way to consistently crash Excel. Here’s the formula that did it
If I remove the TRANSPOSE function, no problemo. But as soon as I type it in, boom. The idea is to perform a two stage lookup in a single array formula. I have this range
And I have this range
I want to sum all of the Indents for each Product. I started by getting an array of the Codes
($A$2:$A$6=A2)*($B$2:$B$6) which resolves to
Next, I put those Codes into a MATCH formula
MATCH(($A$2:$A$6=A2)*($B$2:$B$6),Sheet2!$A$2:$A$6,FALSE) which resolves to
I filtered out the NAs
I offset from Sheet2!B1
When I SUM that up, I get 40. I thought maybe if I TRANSPOSEd the array, I could trick it into summing properly, but instead I get “Microsoft Excel has stopped responding”. I never did find a satisfactory way to get the job done with one formula. I used a helper column and it was easy, just not satisfying.
If you’d like to crash your Excel 2003 (didn’t try other versions), download the file and array enter the formula in cell F1.
You can download recursivelookup.zip