Explanation
In this example the goal is to return rows in the range B5:E15 that have a specific state value in column E. To make the example dynamic, the state is a variable entered in cell H4. When the state in H4 is changed, the formula should return a new set of records. This is a perfect application for the FILTER function, which is designed to return values that meet specific logical criteria from a set of data.
FILTER function
The FILTER function “filters” a range of data based on supplied criteria. In other words, the FILTER function will extract matching records from a set of data by applying one or more logical tests . The result is an array of matching values from the original data. The FILTER function takes three arguments , and the generic syntax looks like this:
=FILTER(array,include,[if_empty])
In this problem, array is given as B5:E15, which contains the full set of data without headers:
=FILTER(B5:E15,
The include argument is an expression that runs a simple test for matching states:
E5:E15=H4 // test state values
Placing this expression into FILTER as the second argument, we have:
=FILTER(B5:E15,E5:E15=H4,
Finally, the optional if_empty argument is set to “not found” in case no matching data is found:
=FILTER(B5:E15,E5:E15=H4,"not found")
Note the value for if_empty is a text string in double quotes (""). You can customize this message as you like. Supply an empty string ("") to display nothing. If you omit if_empty altogether, FILTER will return a #CALC! error when no data is returned.
When the formula above is entered, the include argument is evaluated. Since there are 11 cells in the range E5:E11 and the value in H4 is “TX”, the include expression returns an array of 11 TRUE and FALSE like this:
{TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE}
Notice the TRUE values in this array correspond to records in the data where the State is “TX”. This array is used by the FILTER function to retrieve matching data. Only rows where the result is TRUE make it into the final output.
Other fields and criteria
Other fields can be filtered in a similar way. For example, to filter the same data on orders that are greater than $100, you can use FILTER like this:
=FILTER(B5:E15,C5:C15>100,"not found")
You can also configure the logic inside the include argument to apply more complex criteria .
Explanation
In this example, the goal is to display the biggest 3 gainers and losers in a set of data where Start and End columns contain values at two points in time, and Change contains the percentage change in the values. The data in B5:E16 is defined as an Excel Table with the name data . Two formulas are required, one to return the top 3 gainers in the table, and one to return the top 3 losers. The primary component of the solution is the FILTER function, which is used to extract data that meets specific logical conditions from the table.
If you are new to FILTER, see this video: Basic FILTER function example
Top 3 gainers
In the example shown, the formula to return the top 3 gainers in cell G5 is:
=SORT(FILTER(data,data[Change]>=LARGE(data[Change],3)),4,-1)
Working from the inside out, the first task is to extract the three rows from the data that have the largest change values. To do this, we use the FILTER function together with the LARGE function like this:
FILTER(data,data[Change]>=LARGE(data[Change],3))
Inside FILTER, the array argument is the entire table, since we want to return all four columns. The include argument is a logical test based on the LARGE function:
data[Change]>=LARGE(data[Change],3)
The LARGE function is configured to return the third largest value in the Change column:
LARGE(data[Change],3) // get 3rd largest value
With 3 given for k , LARGE returns the 3rd largest value .02699 (2.699%). We can then simplify the FILTER function to the following:
FILTER(data,data[Change]>=0.0269)
Essentially, we are asking FILTER for rows in the table where change is greater than or equal to 0.0269. FILTER returns the 3 matching rows in an array like this:
{"JOF",111.63,117.546,0.053;
"HHB",8.104,8.323,0.0269;
"XXO",43.124,47.048,0.091}
Note: values have been rounded for readability.
At this point, we have the data we want, but it is not sorted. Since the goal is to show the biggest gainers first, we need to sort the array in descending order by Change. To do this, we use the SORT function directly on the array returned by FILTER:
=SORT(filtered_array,4,-1)
This is an example of nesting - the result from FILTER is delivered as an array inside the SORT function. Since the change column is the last column, sort_index is given as 4, and sort_index is provided as -1 to sort in descending order. Finally, the SORT function returns the final sorted array to cell G5, which spills into the range G5:J7.
Video: Basic SORT function example
Top 3 losers
Next, we need to list the biggest 3 losers. In the worksheet shown, the formula in G12 is:
=SORT(FILTER(data,data[Change]<=SMALL(data[Change],3)),4,1)
The first task is to extract the three rows from the data that have the smallest change values. To do this, we use the FILTER function together with the SMALL function like this:
FILTER(data,data[Change]<=SMALL(data[Change],3))
The array argument in FILTER is given as the table name data , since we want to return all four columns. The include argument is supplied as a logical expression that targets the three smallest values in Change:
data[Change]<=SMALL(data[Change]
The SMALL function returns the third largest change in the table:
SMALL(data[Change],3) // get 3rd smallest value
With 3 as k , SMALL returns -0.0671 (-6.7%). We can now simplify the FILTER function to this:
FILTER(data,data[Change]<=-0.0671)
We are asking FILTER for rows in data where Change is less than or equal to -0.0671. FILTER returns the 3 matching rows in an array like this:
{"EYN",7.673,7.158,-0.067;
"YOL",17.492,16.058,-0.082;
"DPP",4.067,3.790,-0.068}
Note: values have been rounded for readability.
We have the data we want, but it is unsorted. The goal is to show the biggest losers first, so we want to sort the array in ascending order by change. To do this, the FILTER function is nested inside the SORT function , and the result from FILTER is delivered as array:
=SORT(filtered_array,4,1)
Sort_index is given as 4, since change is the fourth column, and sort_index is provided as 1 because we want to sort ascending order, starting with the largest negative change. Finally, the SORT function returns the final sorted array to cell G12, which spills into the range G12:J14.
Note: this formula will always return the three rows in the data with the smallest change values, whether these values are negative or not. The label “losers” may not make sense in a small set of data with no negative change values.
