Learn Excel
EN
EnglishENNederlandsNL

Home › Excel functions › LARGE & SMALL

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

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.

LARGE & SMALL · 2 min read
In this guide
  1. Syntax
  2. Top 3 example
  3. Names for the top 3
  4. More uses
  5. Errors
  6. 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

ABCDE
1RepSalesRankSales
2Anna1,20011,200
3Ben8002950
4Chris9503800
5Dana450
6Eli700
=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.