LARGE Function

Returns the k-th largest number in a set.

Category

Statistical

Available in

All versions, Microsoft 365

Returns

A number

Taught hands-on in Level 3: Intermediate Lookups & Data Summaries · Conditional Summaries module

Overview

LARGE(range, 1) is the largest value (same as MAX), LARGE(range, 2) the second largest, and so on. Tied values each take a position.

It's the classic way to build a top-3 or top-10 list.

Syntax

=LARGE(array, k) Explain

Arguments

ArgumentRequired?What it does
arrayYesThe range or array of numbers.
kYesThe position from the top: 1 = largest.

Examples

Example 1 — Top 3 sales results

Column D holds the place; lock the range with $ so the formula fills down.

E2fx=LARGE($C$2:$C$8,D2)
ABCDE
1RepRegionSalesPlaceSales
2AnaNorth62000171250
3BenSouth48500262000
4CyNorth50000355400
5DiEast71250
6EdSouth39800
7FayNorth55400
8GusEast48500
=LARGE($C$2:$C$8,D2) Explain

Example 2 — Sum of the top 3

Give LARGE several positions at once.

=SUM(LARGE(C2:C8,{1,2,3})) Explain

Returns 188,650.

Example 3 — Who's top?

Pair LARGE with XLOOKUP (or INDEX/MATCH) to return the name.

=XLOOKUP(LARGE(C2:C8,1),C2:C8,A2:A8) Explain

Returns Di.

Common mistakes

  • #NUM! k is 0 or less, or bigger than the number of values.
  • Ties. LARGE(…,2) and LARGE(…,3) can return the same number when values tie, and a lookup on it will find the first match twice.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.