MAX gives the biggest value, but what about the 2nd or 3rd biggest? LARGE returns the nth largest value in a range, and SMALL returns the nth smallest — the backbone of top-N and bottom-N reports.
The example
Scores. We want the 2nd highest.
| A | B | |
|---|---|---|
| 1 | Player | Score |
| 2 | Ana | 95 |
| 3 | Ben | 88 |
| 4 | Cy | 74 |
| 5 | Dot | 82 |
| 6 | 2nd largest: | 88 |
The formula
The second-highest score:
How it works
LARGE ranks the values and returns the one you ask for:
- Internally LARGE sorts the range high to low: 95, 88, 82, 74.
LARGE(B2:B5, 2)returns the value at rank 2 —88.LARGE(…, 1)equals MAX;SMALL(…, 1)equals MIN.- Point the rank at a cell (
LARGE(B2:B5, D1)) to make a flexible “nth” report.
Try it: interactive demo
Pick a rank and direction.
Variations
Nth smallest
SMALL with the rank:
Nth largest ignoring duplicates
The 2nd distinct value (Excel 365):
Name of the nth largest
Look up who scored it:
Pitfalls & errors
#NUM! if n is too big or < 1. Asking for the 5th largest of 4 values errors. Keep n between 1 and the count.
Duplicates take separate ranks. Two 95s mean LARGE(...,1) and LARGE(...,2) are both 95. Wrap the range in UNIQUE for distinct ranking.
LARGE ignores text and blanks but counts zeros — a 0 entered for “no score” participates in the ranking.
Practice workbook
Frequently asked questions
How do I find the 2nd or 3rd largest value in Excel?
How do I find the nth smallest value?
How do I get the nth largest unique value?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class