Dynamic arrays are difficult to explain, because the work in way that is not intuitive with how we are taught to perform calculations during are formative school years. But let me put in a nutshell for you, remember all the times you have heard someone talk about the ability to multi-task. You know, women can multi-task and men cannot.
Well, think of the dynamic array as the women, it is basically an ability to perform several calculations from one function simultaneously.
Excel Dynamic Array formulas were came about n 2018 and in the Excel world they are perhaps the most significant change to Excel formulas in years. Dynamic Arrays allow you to work with multiple values at the same time in a formula.
This feature enables Excel formulas to return numerous results based on a single formula entered in one cell. The results are also dynamic, meaning that if the source data changes, the results will dynamically update.
A dynamic array formula
The result
The process of returning multiple values from one Excel array formula is referred to as ‘spilling’ because the results appear to spill across the worksheet.
Once you know how to use and exploit Dynamic Array functions you will add a layer of serious power and flexibility to your excel skills, because even seasoned Excel users often struggle with dynamic arrays.