How to Use LARGE/SMALL in Google Sheets
=LARGE(data, n) and =SMALL(data, n).
What LARGE and SMALL do
MAX and MIN give you the top and bottom value. LARGE and SMALL go further: you pick the rank. Use them for the second-highest score, the third-cheapest price, or a top-5 list built one cell at a time.
Syntax
=LARGE(data, n)
=SMALL(data, n)
data: the range or array that holds the values.n: the rank to return. For LARGE,1is the largest,2the second largest, and so on. For SMALL, it counts up from the smallest.
Google's help pages show that n can also come from a cell, as in LARGE(A2:B100, C2).
A step-by-step example
Here are five test scores in cells A1:B6.
| A | B | |
|---|---|---|
| 1 | Name | Score |
| 2 | Ann | 82 |
| 3 | Ben | 64 |
| 4 | Cara | 91 |
| 5 | Dev | 77 |
| 6 | Eli | 58 |
To find the second-highest score:
=LARGE(B2:B6, 2)
This returns 82. Here is why:
- Sorted from largest to smallest, the scores are 91, 82, 77, 64, 58.
- The second value in that order is 82.
For the lowest scores, SMALL counts from the bottom. In the same list, =SMALL(B2:B6, 3) returns 77 (58, 64, then 77).
Build a top list
Put the ranks 1, 2 and 3 in a helper column and point n at those cells. Copying one formula down gives you the top three scores without any sorting.
Common mistakes
- An n that is too large. Asking for the 8th largest value in a list of five returns an error.
- Mixing up the pair. LARGE counts from the top, SMALL from the bottom.
- Wanting the name, not the score. LARGE returns the value. Pair it with INDEX and MATCH to look up who earned it.
- Duplicate values. Two equal scores each take a rank of their own.
Try it yourself
Use the score table above (A1:B6). Type your formula into the box, starting with =, then press Check.
Exercise 1: Second highest
Write a formula that returns the second largest score in B2:B6.
Exercise 2: Third lowest
Write a formula that returns the third smallest score in B2:B6.
What to learn next
- MIN and MAX: find the smallest and largest values.
- INDEX and MATCH: look up a value in any direction.
- AVERAGE: find the mean of a range.
- MEDIAN: find the middle value of a range.
Syntax and rules in this guide were checked against Google's LARGE/SMALL help page. See all guides.