The LARGE and SMALL functions in Excel
LARGE returns the value at position k when a list is sorted from largest to smallest. LARGE(…, 1) is the highest value, LARGE(…, 2) the second highest, and so on. SMALL works the same way from the bottom: 1 is the lowest value.
Use them to find the top three sales, the second-cheapest supplier, or the best result that still counts. Unlike MAX and MIN, they can reach past the first place.
Syntax
=LARGE(array, k)
| Argument | What it means |
|---|---|
| array | The range or list of numbers to look in, e.g. A2:A7. |
| k | The position to return: 1 for the largest (or smallest), 2 for the next one, and so on. |
Examples
1. Second-highest score
| A | B | C | |
|---|---|---|---|
| 1 | Score | 2nd highest | |
| 2 | 72 | 88 | |
| 3 | 91 | ||
| 4 | 65 | ||
| 5 | 88 | ||
| 6 | 84 | ||
| 7 | 54 |
In order from the top the scores are 91, 88, 84… The 2nd value is 88, which is what LARGE returns.
2. Lowest score that is not the minimum
| A | B | C | |
|---|---|---|---|
| 1 | Score | 3rd lowest | |
| 2 | 72 | 72 | |
| 3 | 91 | ||
| 4 | 65 | ||
| 5 | 88 | ||
| 6 | 84 | ||
| 7 | 54 |
From the bottom the scores are 54, 65, 72… so the 3rd smallest is 72. Use SMALL for the bottom of a list.
Common mistakes
Using k larger than the list. If the range holds 6 numbers, a k of 7 returns #NUM!.
Fix: Check the count with COUNT first, and keep k between 1 and that count.
Blank or text cells in the range. They are ignored, so k counts only the numbers.
Fix: This is usually what you want, but check that every value you expect is a number, not text.
Expecting the first value to be the top one. LARGE sorts by value, not by row order.
Fix: Use the value's rank k, or pair it with INDEX and MATCH to find which row it belongs to.
Frequently asked questions
What is the difference between LARGE and MAX?
MAX is the same as LARGE with k = 1. LARGE lets you pick the 2nd, 3rd or any other position.
Does LARGE handle duplicate values?
Yes. Two equal values take two positions, so a list of 91, 91, 88 gives 91 for k = 1 and k = 2.