In this video, we'll look at how to nest dynamic array formulas together.
One of the most powerful ways to extend the functionality of dynamic array formulas is to nest one function inside another.
To illustrate, let's look at an example based on the SORT and FILTER functions.
Here we have data that shows over 300 of the largest cities by population in the United States.
This data is in an Excel Table called "Table1".
We have rank, city, state, population, and percent change.
Currently, the data is sorted by population.
The goal is to filter the data by state, sorted in descending order by percent change.
I've already set up a dropdown list so we can easily select a state.
I'll start with the FILTER function and filter on state.
For array, we want the entire table.
For the include argument, we check all states against the value in J4.
When I enter the formula, we get a filtered list.
And, when I change the state, we get a new subset of data.
Notice the filtered data is listed in the same order as the source data, with largest cities on top.
However, we want to sort by percent change.
The trick is to pipe the results from the FILTER function directly into the SORT function.
In other words, we need to nest FILTER inside the SORT function.
To start, I'll cut the existing formula to the clipboard. Then I'll enter the SORT function.
Now, when we get to array, what do we want to enter?
Well, we could enter all data, but that would just bypass FILTER.
Instead, we want to give SORT already filtered data, so I just paste the original FILTER formula from the clipboard.
Now we need to provide a sort index, which is 5 in this case, and sort direction, which is -1, since we want to sort in descending order.
Now when I enter the formula we have filtered results sorted by percent change.
The key thing to understand here is that the FILTER function is passing a result directly into the SORT function.
In other words, SORT is only working with data that has already been filtered.
The Excel SORT function sorts the contents of a range or array in ascending or descending order. Values can be sorted by one or more columns. SORT returns a dynamic array of results.
The Excel FILTER function filters a range of data based on supplied criteria, and extracts matching records.
Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.
Quick, clean, and to the point.