Exceljet

Quick, clean, and to the point

FILTER on top n values with criteria

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

To filter data to show the top n values that meet specific criteria, you can use the FILTER function together with the LARGE and IF functions. In the example shown, the formula in F5 is:

=FILTER(data,(score>=LARGE(IF(group="b",score),3))*(group="b"))

where data (B5:D16), group (C5:C16) 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 and IF functions. The result is the top 3 scores in group B.

The FILTER function applies criteria with the include argument. In this example, criteria are constructed with boolean logic like this:

(score>=LARGE(IF(group="b",score),3))*(group="b")

The left side of the expression targets scores greater than or equal to the 3rd highest score in group B:

score>=LARGE(IF(group="b",score),3)

The IF function is used to make sure LARGE is only working with group B scores. Because we have 12 scores total, IF returns an array with 12 results like this:

{FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83}

Notice the only scores that survive the operation are from Group B. All other scores are FALSE. This array is returned directly to LARGE as the array argument:

LARGE({FALSE;65;FALSE;80;FALSE;88;FALSE;76;FALSE;86;FALSE;83},3)

LARGE ignores the FALSE values and returns the 3rd highest score, 83.

We can now simplify the formula to:

=FILTER(data,(score>=83)*(group="b"))

which resolves to:

=FILTER(data,{0;0;0;0;0;1;0;0;0;1;0;1})

Finally, FILTER returns records for Mason, Annie, and Cassidy, 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(IF(group="b",score),3))*(group="b")),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.