Summary

In this chapter, you learned about the concept of dynamic array functionality in Excel. Some functions, such as UNIQUE, SORT, SORTBY, FILTER, RANDARRAY, and SEQUENCE, were written from the ground up as dynamic array functions. The XLOOKUP function is a hybrid that can work as a replacement for the VLOOKUP function. Alternatively, it can work as a modern dynamic array function returning multiple results to a block of cells or summarized within another function, such as the SUM or AVERAGE functions.

Additionally, by way of the spreadsheet that we put together in the Dynamic amortization schedule section, we saw that longstanding functions in Excel such as EOMONTH, IPMT, PPMT, and SUMIF can all take on dynamic characteristics. We used the spilled range operator (#) in conjunction with referencing a set of data returned by a dynamic array function.

Dynamic array functions empower you to streamline your formulas, as a single formula can return results to hundreds or even...