Nesting dynamic array formulas

To illustrate, let’s look at an example based on theSORTandFILTERfunctions.

Here we have data that shows over 300 of the largest cities by population in the United States.

This data is in anExcel Tablecalled “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 theincludeargument, 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.

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 tonestFILTER 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.

Now when I enter the formula we have filtered results sorted by percent change.

In other words, SORT is only working with data that has already been filtered.