Abstract
Transcript
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 can 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.
