In fall 2018, Microsoft announced new functionality in Excel called "dynamic array formulas". Dynamic array formulas have the ability to natively work with multiple values and can "spill" these results onto the worksheet.
The new functionality is provided in two parts:
- New functions designed to leverage dynamic array behavior directly
- New formula engine that lets older formulas and functions use dynamic arrays.
This is a big upgrade and a radical change to the way Excel behaves in some circumstances, and it is only available in Excel 365 and Excel 2021 for now. This means all other versions of Excel, including 2016 and 2019, do not have dynamic arrays.
For a quick summary of dynamic arrays, see: Dynamic Array
For details and examples, see: Dynamic Array Formulas in Excel