📎 This article includes 1 downloadable practice file ↓

“Show me the five biggest orders from the North region this quarter.” It’s one of the most common management requests, and plain LARGE can’t do it because LARGE doesn’t take a condition. These combinations can.

The nth largest value with a condition

Excel 365 / 2021

=LARGE(FILTER(I2:I201, B2:B201="North"), 2)      ' 2nd biggest North order

Any version: AGGREGATE

=AGGREGATE(14, 6, I2:I201 / (B2:B201="North"), 2)

Function 14 is LARGE, option 6 ignores errors. Dividing by the condition turns non-North rows into #DIV/0! errors, which AGGREGATE then skips — a neat trick that needs no array entry.

The whole top 5 table (Excel 365)

=TAKE(SORT(FILTER(A2:I201, B2:B201="North"), 9, -1), 5)

1. FILTER keeps North rows (all 9 columns).
2. SORT(…, 9, -1) sorts by column 9 (Amount), largest first.
3. TAKE(…, 5) keeps the first five rows.

Add more conditions by multiplying them: (B2:B201="North")*(A2:A201>=DATE(2026,7,1)).

Top N in older Excel

Get the values with AGGREGATE (k = 1 to 5 in a helper column), then fetch the customer for each:

L2: =AGGREGATE(14, 6, $I$2:$I$201/($B$2:$B$201="North"), ROWS(L$2:L2))
M2: =INDEX($D$2:$D$201, AGGREGATE(15, 6, (ROW($I$2:$I$201)-1)/(($I$2:$I$201=L2)*($B$2:$B$201="North")), COUNTIF(L$2:L2, L2)))

The second formula finds the row of the matching amount; the COUNTIF part makes ties return different rows instead of the same customer twice.

Top N per group

For “top 3 in every region”, rank within the group (see rank within group) and filter rank ≤ 3. In 365, GROUPBY can also return a top-N summary directly in the newest builds.

Where people go wrong

• LARGE with IF in old Excel returns one wrong value unless entered with Ctrl+Shift+Enter — AGGREGATE avoids that.
• Ties return the same row twice with plain MATCH — use the COUNTIF tie-breaker above or SORT/TAKE in 365.
• #CALC! — FILTER found nothing. Give it the third argument: FILTER(…, …, "No rows").
• Asking for k larger than the matches — #NUM! from LARGE/AGGREGATE. Wrap with IFERROR or check the count first.

Practice

The combo practice workbook below has a Sales sheet of 200 orders and a task for every formula on this page. Type your formula in the yellow column; the check turns green when the answer matches. The Answers sheet has working versions.

More combinations: all formula combos · functions used here are explained in the Excel function course.