"Attitude is a little thing that makes a big difference." [Winston Churchill]
Excel © array formulas are powerful. They can be short and fast.
But: Many Excel users do not understand them. They are not able to adapt or to maintain them. Quite often the runtime increases drastically if more and more records are added to the array.
My w-rule for Excel array formulas is:
U1) understand them and
U2) u(a)void them.
U1 - Understanding Excel array formulas
See, for example, Chip Pearson's introduction page on array formulas.
U2 - U(a)void Excel array formulas
Array formulas should only be used if a series of cells is dependent on each other. See my solution for unique random integers, for example.
A nice example where you should NOT apply array formulas is shown below - but of course it is an interesting example to get more insight into them...