Syntax
LARGE(array, k)
| Parameter | Description |
|---|---|
| array | Parameter of the LARGE function. |
| k | Parameter of the LARGE function. |
Examples
Second highest sales
Formula
=LARGE(B2:B50, 2)
Returns the second-largest value in the sales column. If the top sale was $45,000 and the next was $38,000, returns 38000.
Top 3 test scores
Formula
=LARGE(D2:D200, {1,2,3})
When entered as an array formula or spilled, returns the 1st, 2nd, and 3rd highest test scores simultaneously.
Dynamic ranking
Formula
=LARGE($C$2:$C$100, ROW()-1)
In a list starting at row 2, each row returns the next largest value. Row 2 gets the largest, row 3 gets 2nd largest, and so on.
Common Errors
#NUM!
Returned when k is less than 1 or greater than the number of values in the array. For example, =LARGE(A1:A5, 10) errors because there are only 5 values.
#VALUE!
Occurs when k is not a number.
Tips
LARGE(array, 1) equals MAX
The 1st largest value is the same as MAX. Use LARGE when you need anything other than the absolute maximum.
Handle ties
If two values tie for 2nd place, LARGE returns the same value for k=2 and k=3. The next distinct value would be at k=4.
Build top-N lists
Combine LARGE with INDEX/MATCH to build a top-N leaderboard: =INDEX(names, MATCH(LARGE(scores,1), scores, 0)) gets the name of the top scorer.
Try LARGE in Viztab
Import your data and use LARGE with 370+ other formulas. No signup required.
Open Viztab