Explanation
Note: FILTER is a newdynamic arrayfunction inExcel 365.
In other versions of Excel, there arealternatives, but they are more complex.
In addition, the result should include the Group column, sorted in the same way.
Typically FILTER is used to filter data vertically, selecting rows that match provided conditions.
However, FILTER can also select data horizontally.
In Boolean algebra,multiplication corresponds to AND logic, and addition corresponds to OR logic.
In this case, we want FILTER to return the Group column and the matching year column.
This means we need OR logic - i.e.
column = “group” OR column = [year].
When this array is provided to FILTER as theincludeargument, FILTER returns columns 1 and 3 from the data.
Sort by row
Because the FILTER function isnestedinside theSORT function.
With these inputs, theSORT functionreturns the sorted as shown in the example.
Notice that Group E appears first since 27% is the highest value in 2017.
If you are new to data validation, see ourData Validation Guide.
The output from FILTER is dynamic.
If source data or criteria change, FILTER will return a new set of results.
Values can be sorted by one or more columns.
SORT returns a dynamic array of results.
Related videos
FILTER function basic example
Basic SORT function example
