I have always tried to use arrays in Excel without creating what is referred to as an “Array Formula.” An array formula is easily identified by the fact that you have to press Ctrl+Shift+Enter to create one, which is why they are sometimes referred to as CSE formulas. It has been my opinion that you can accomplish everything an array formula is capable of by nesting functions that create arrays in other functions.* The formulas this creates are more flexible, which makes building large models a little simpler.
The first video below explains the difference between an array (as it is used in this post) and an array formula (as it is used by the Excel community). It then demonstrates how you can use regular formulas with nested functions. The second video demonstrates how this knowledge might be applied in a financial model, and the third video provides a more complex example. If you are new to advanced formulas I would recommend starting with the first video.
VIDEO 1 (Template for Download):
VIDEO 2 (Link to Template):
VIDEO 3 (Link to Template):
*Applies to my experience building financial models.