FILTER on top n values with criteria

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.

April 14, 2025 · 1 min · 68 words · Timothy Mendez

Table of Contents

The result is the top 3 scores in group B.

The FILTER function applies criteria with theincludeargument.

All other scores are FALSE.

Excel formula: FILTER on top n values

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.

Excel formula: Basic filter example

SORT returns a dynamic array of results.

How to show top or bottom n results

Excel formula: Filter this or that

Excel formula: Filter text contains

Excel formula: Filter exclude blank values

Excel formula: Name of nth largest value

Excel FILTER function

Excel LARGE function

Excel SORT function