LARGE in Excel: what it does and how to use it

LARGE returns the nth biggest value in a range: the second largest, the third, whichever position you ask for. SMALL does the same from the bottom.

Syntax

LARGE(array, k)

When to use it

Top three lists, the runner up, the second highest sale of the month.

What trips people up

It works on position, not on distinct values, so if the top two figures are identical then the first and second largest are the same number. Asking for a position beyond the end of the list returns #NUM!.

Watch it built, step by step

The third biggest value — Top three, not just the top one

Questions

How do I find the smallest values instead?

SMALL works the same way: =SMALL(B:B,1) is the lowest, =SMALL(B:B,2) the second lowest.

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

=INDEX(A:A,MATCH(LARGE(B:B,1),B:B,0)). If two rows share the value, this returns the first of them.

What happens when two values are tied?

LARGE returns the same value twice. If the top two are both 500, then LARGE(...,1) and LARGE(...,2) are both 500.

Related functions

RANK — RANK gives the position of a value within a list, counting from the largest by default or from the smallest if you ask.
MAXIFS — MAXIFS returns the largest number in a column, but only from the rows that meet the conditions you set.

Formula Steps Watch how Excel formulas are built. Download the sheet.

Settings: separator, Excel version

Argument separator

Set by your computer's language, not by the file. A frequent cause of a correct-looking formula being rejected.

Excel version

XLOOKUP, IFS, TEXTBEFORE and TEXTJOIN need Microsoft 365 or 2021; older versions return #NAME? instead. Choosing the older one switches every walkthrough to a formula that works there.