LARGE and SMALL in Excel: Find the Top 3 or Bottom 5

Q: How do I handle ties in a top 3 lookup?

In Microsoft 365, use SORT and TAKE as shown above; it keeps each row together. In older versions, add a tiny tiebreaker to the values, such as =B2+ROW()/100000, and rank on that helper column.

Q: What's the difference between LARGE and MAX?

MAX only returns the largest value. LARGE can return the 2nd, 3rd or any nth largest value; LARGE(range, 1) equals MAX.

Q: Can LARGE work with conditions?

Yes, in Microsoft 365: =LARGE(FILTER(B2:B6, A2:A6="North"), 1) returns the largest North value.

LARGE returns the nth largest value in a range and SMALL the nth smallest. They're how you build a top 3, find the runner-up, or average the five lowest scores.

Excel CSE · 2 min read

In this guide

Syntax
Top 3 example
Names for the top 3
More uses
Errors
FAQ

Syntax

=LARGE(array, k) =SMALL(array, k)

k is the position: 1 for the largest (or smallest), 2 for the second, and so on. =LARGE(range, 1) is the same as MAX.

Example: a top 3

	A	B	D	E
1	Rep	Sales	Rank	Sales
2	Anna	1,200	1	1,200
3	Ben	800	2	950
4	Chris	950	3	800
5	Dana	450
6	Eli	700

=LARGE($B$2:$B$6, D2)

With the ranks 1, 2 and 3 in column D, copying this down gives 1,200, 950 and 800. For the bottom 3, use SMALL: 450, 700, 800.

Show the names that go with them

=INDEX($A$2:$A$6, MATCH(E2, $B$2:$B$6, 0))

This looks up who has the value in E2: Anna, Chris and Ben. If two people have exactly the same value, both rows return the first name; see the FAQ for a fix.

In Microsoft 365 or Excel 2021+ you can get a complete top 3, names included, in one formula: =TAKE(SORT(A2:B6, 2, -1), 3).

More uses

Sum of the top 3: =SUM(LARGE(B2:B6, {1,2,3})) returns 2,950.
Average without the lowest score: =(SUM(B2:B6)-SMALL(B2:B6, 1))/(COUNT(B2:B6)-1).
Next upcoming date: =SMALL(D2:D100, COUNTIF(D2:D100, "<"&TODAY())+1) returns the first date from today onwards.

Errors

#NUM! means k is bigger than the number of values (asking for the 6th largest of 5 numbers), or k is 0 or negative.

Frequently asked questions

How do I handle ties in a top 3 lookup?

In Microsoft 365, use SORT and TAKE as shown above; it keeps each row together. In older versions, add a tiny tiebreaker to the values, such as =B2+ROW()/100000, and rank on that helper column.

What's the difference between LARGE and MAX?

MAX only returns the largest value. LARGE can return the 2nd, 3rd or any nth largest value; LARGE(range, 1) equals MAX.

Can LARGE work with conditions?

Yes, in Microsoft 365: =LARGE(FILTER(B2:B6, A2:A6="North"), 1) returns the largest North value.