Statistics

Excel LARGE and SMALL Functions: Find the Nth Biggest or Smallest

Return the nth largest or nth smallest value in a range.

MAX gives you the biggest value. LARGE gives you the nth biggest, which is what you actually need whenever the question is about a top three rather than a top one. SMALL is its mirror, returning the nth from the bottom.

Their main job is building a top-N table without sorting the source. =LARGE($B$2:$B$100, 1) in one cell, then 2, then 3 down the column, gives you a ranked list that updates itself while the underlying data stays in whatever order it arrived in. Replacing the hard-coded number with ROW()-1 means you can fill it down instead of typing each one.

One behaviour worth knowing: duplicates each take a place. If the top two values are both 28,000, LARGE with k=1 and k=2 both return 28,000. That is usually right — the second largest value really is 28,000 — but it means a top-five list can show the same number twice.

Syntax

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

Arguments

array
Required
The range to look in. Text and blanks are ignored.
k
Required
Which position to return, counting from the top for LARGE and from the bottom for SMALL. k of 1 is equivalent to MAX or MIN.

The example data

Headers in row 1, data in A2:C8. Note how far F8's salary sits above the rest.

ABC
1EmployeeSalaryTeam
2Alice Moreau32000Support
3Bruno Santos28000Support
4Chen Wei45000Engineering
5Dana Okafor28000Support
6Erik Halls38000Engineering
7Farah Idris41000Engineering
8Greg Nolan154000Executive

Worked examples

=LARGE(B2:B8, 1)

Result: 154000

The largest salary. Identical to MAX for k of 1.

=LARGE(B2:B8, 3)

Result: 41000

The third largest — the question MAX cannot answer.

=SMALL(B2:B8, 2)

Result: 28000

The second smallest. Both 28,000 entries count separately, so k of 1 and 2 return the same figure.

=LARGE($B$2:$B$8, ROW()-1)

Result: 154000

The fill-down pattern. On row 2 this is k=1, on row 3 it is k=2, so one formula builds the whole ranked column.

Now practise it

Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.

Open the exercise: LARGE and SMALL Functions

Common errors and how to fix them

#NUM!

Why it happens: k is zero, negative, or larger than the count of numbers in the range — asking for the 8th largest of seven values.

How to fix it: Guard the fill-down: =IFERROR(LARGE($B$2:$B$8, ROW()-1), "") so the column stops cleanly when it runs out.

#VALUE!

Why it happens: k is text, or array contains an error value.

How to fix it: Check k resolves to a number. If it is ROW()-1, make sure the formula starts on the row you think it does.

The same value appears twice in a top-N list

Why it happens: Duplicates each occupy a position.

How to fix it: That is correct behaviour. For distinct values, run LARGE over UNIQUE(range) instead.

Tips worth knowing

  • =LARGE(range, 1) is MAX and =SMALL(range, 1) is MIN — use the clearer name when k is fixed at 1.
  • Pair with INDEX and MATCH to get the *name* attached to the nth value, not just the number.
  • SUM of LARGE with an array constant totals a top-N: =SUM(LARGE(B2:B8, {1,2,3})).
  • With dynamic arrays, TAKE(SORT(range, 1, -1), 3) is usually a clearer top-three than three LARGE calls.

Frequently asked questions

How do I build a top five list?

Put =LARGE($B$2:$B$100, ROW()-1) in the first cell and fill down five rows, wrapping it in IFERROR so it degrades gracefully. With dynamic arrays, =TAKE(SORT(B2:B100, 1, -1), 5) does the same in one formula.

How do I get the name that goes with the largest value?

Use INDEX and MATCH around it: =INDEX($A$2:$A$8, MATCH(LARGE($B$2:$B$8, 1), $B$2:$B$8, 0)). Note this returns the first match, so tied values always show the same name.

What is the difference between LARGE and MAX?

MAX only ever returns the single biggest value. LARGE takes a position argument, so LARGE(range, 1) equals MAX but LARGE(range, 3) gives the third biggest, which MAX cannot express.

Related functions

Guides that use it

Longer reads where this function does real work in a real sheet.

All functions