Dynamic array formulas are useful especially when users want to implement effective utilization of data cache in the spreadsheets. This is possible because these formulas allow random access with low memory footprints.
When a cell contains a dynamic array formula, multiple values are returned because the elements of the array spill into the adjacent empty cells. Unlike generic arrays, dynamic arrays automatically resize when the data is inserted or removed from the source range.
Dynamic array formulas that return more than one result and spill successfully to the nearby cells with values spanning to a cell range containing multiple rows and columns are known as spilled array formulas.
For more information on dynamic array formulas and its examples, refer to the following topics:
