MAX gives only the biggest value. LARGE gives the biggest, the second biggest, the third biggest, and so on. SMALL does the same from the bottom.
Syntax
=LARGE(array, k) and =SMALL(array, k)
k is the position: 1 is the highest (or lowest), 2 is the next, and so on.
Second highest salary
=LARGE(D2:D200, 2)
Top 5 sales in a list
Type 1 to 5 in cells F2:F6, then in G2 use:
=LARGE($D$2:$D$200, F2)
Copy down. You now have the top five values.
Show the name for each value
Use INDEX and MATCH to bring the name next to the value:
=INDEX($A$2:$A$200, MATCH(G2, $D$2:$D$200, 0))
If two people have the same value, this returns the first name for both. Use a helper column with a tiny tie breaker, or the SORT function, to handle ties.
Sum of the top 3
=SUM(LARGE(D2:D200,{1,2,3}))
The curly brackets give LARGE three k values at once.
Common mistakes
- k larger than the number of values gives #NUM!.
- Text and blank cells are ignored, which is usually what you want.
Worked example
Sales in B2:B20. =LARGE(B2:B20,1) is the highest, =LARGE(B2:B20,3) is the third highest, and =SMALL(B2:B20,2) is the second lowest. Top 3 total: =SUM(LARGE(B2:B20,{1,2,3})).
Common mistakes
- Using k larger than the number of values, which gives #NUM!.
- Expecting the name. LARGE returns the number only. Use INDEX MATCH to get the name.
- Duplicates return the same number again for the next k.
Frequently asked questions
How do I get the name of the third highest?
=INDEX(A2:A20,MATCH(LARGE(B2:B20,3),B2:B20,0)).
LARGE or MAX?
MAX is the same as LARGE with k = 1. LARGE lets you pick the nth.
Does LARGE ignore blanks and text?
Yes.
Practice this online, free
Reading is only half of learning Excel. Cellgrids is a free place for online Excel practice: solve Excel formula exercises in a live spreadsheet, prepare for Excel interview questions, and work through business case studies built for data analytics and business analytics roles.
Practice these formulas: LARGE, SMALL, INDEX.
Start Excel practice → Case studies
