LARGE returns the Nth highest value. SMALL returns the Nth lowest. Use them for top-N analysis, league tables, and ranking without sorting.
LARGE returns the k-th largest value from a range. SMALL returns the k-th smallest. Together they power top-N analysis, league tables, podium calculations, and ranked comparisons — all without sorting your data.
=LARGE(array, k) and =SMALL(array, k)| Argument | Description |
|---|---|
| =LARGE(array, k) and =SMALL(array, k) | |
| array required | The range of numbers to rank. |
| k required | Which position to return. 1 = highest (LARGE) or lowest (SMALL), 2 = second, and so on. |
=LARGE(B2:B100, 1)LARGE(range,1) is identical to MAX.
=LARGE(B2:B100, 2)MAX can't do this — LARGE can.
=AVERAGE(LARGE(B2:B100,{1,2,3}))Array formula averaging the top 3 values.
=SMALL(B2:B100, 1)=SMALL(B2:B100, 2)Useful when the lowest value is an outlier and you want the next-lowest.
LARGE and SMALL are the formulas of choice when you need ranked values without sorting your data. A podium display (1st, 2nd, 3rd) uses LARGE(range,1), LARGE(range,2), LARGE(range,3). A league table uses LARGE to pull values in order. A worst-performer report uses SMALL.
The real power comes combining LARGE with INDEX MATCH to return not just the value but the name of who achieved it. =INDEX(A2:A100,MATCH(LARGE(B2:B100,1),B2:B100,0)) returns the name of the top performer. Watch out for ties — if two people score the same, MATCH returns the first one found.
For a dynamic top-5 table, put 1, 2, 3, 4, 5 in cells E2:E6, then in F2: =LARGE($B$2:$B$100,E2). Copy down. Now changing the numbers in column E updates the entire ranking table instantly — no sorting required.
If multiple cells have the same value, LARGE counts each separately. LARGE({100,100,90},1) returns 100 and LARGE({100,100,90},2) also returns 100. This is mathematically correct but worth noting when building unique rankings.
ExcelPro has exercises covering this formula across multiple tracks. Free to start.
Try exercises →