Exceljet

Quick, clean, and to the point

FILTER on top n values

Excel formula: FILTER on top n values
Generic formula 
=FILTER(data,range>=LARGE(range,n))
Summary 

To filter a set of data to show the top n values you can use the FILTER function together with the LARGE function. In the example shown, the formula in F5 is:

=FILTER(data,score>=LARGE(score,3))

where data (B5:D16) and score (D5:D16) are named ranges.

Explanation 

This formula uses the FILTER function to retrieve data based on a logical test constructed with the LARGE function.

The LARGE function is a simple way to get the nth largest value in a range. Simply provide a range for the first argument (array), and a value for n as the second argument (k):

 =LARGE(range,1) // 1st largest
 =LARGE(range,2) // 2nd largest
 =LARGE(range,3) // 3rd largest

In this example, the logic for FILTER is constructed with this snippet:

score>=LARGE(score,3)

which returns TRUE when a score is greater than or equal to the 3rd highest score. Since there are 12 cells in the range D5:D16, this expression returns an array of 12 TRUE and FALSE values in an array like this:

{FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}

This array is returned directly to the FILTER function as the include argument, where it is used to filter the data. Only rows where the result is TRUE make it into the final output. The result from the formula in F5 includes the 3 top scores in the data, which spill into the range F5:H7.

Sort results by score

By default, FILTER will return matching records in the same order they appear in the source data. To sort results in descending order by score, you can nest the original FILTER formula inside the SORT function like this:

=SORT(FILTER(data,score>=LARGE(score,3)),3,-1)

Here, FILTER returns results directly to the SORT function as the array argument. Sort index is set to 3 (score) and sort order is set to -1, for descending order.

Dynamic Array Formulas are available in Excel 365 only.
Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.