Array Vs Non-Array (What are the Pros & Cons?)

i first got exposed to a non-array way of doing a formula by user Marcol. the problem with array formulas is that it slows down Excel (especially in a big file) & for basic users, it is definitely easier to press ENTER rather than CTRL + SHIFT + ENTER. While the 2nd reason is definitely valid, i'm not sure about the speed. i couldnt find any source that had such testing, so i simply assumed it's faster until i recently had the time to test the results.

In the file attached are 3 worksheets with different formulas in Column G. the test i did actually separates this 3 worksheets into 3 workbooks instead. and instead of just 5 rows each, i had 25,000 rows (A2:C25000 & G2:G25000). you just have to copy the formulas down if you want to. for convenience's sake, i shall not upload 3 workbooks of 25,000 rows, with each file around 5MB.

I used 3 methods; 1st sheet using the array formula, 2nd one using the INDEX method Marcol introduced me to, & the 3rd using a new formula in Excel 2010 called AGGREGATE
so the results are surprisely as such....

Methods	Speed of Calculation	File Size
Array	1:01 min	5410 KB
Non-Array with INDEX	1:19 min	5215 KB
Non-Array with Aggregate	0:58 min	4920 KB

so the 2nd method is actually slower than the 1st, despite it being a non-array formula. The file size though, is a little smaller. It's only the 3rd method with the new formula that actually saves in speed & size.

Comments, anyone?