Find the top 3 values in Excel with LARGE and SMALL
MAX gives the largest value; LARGE gives the second and third. How to list the top 3 or bottom 3, show who they belong to, and handle ties.
To find the top 3 values in Excel, use LARGE. =LARGE(B3:B10,1)
is the largest, =LARGE(B3:B10,2) the second, and
=LARGE(B3:B10,3) the third. SMALL does the same from the bottom.
Here is a bakery's sales for one month, under a title in row 1. The owner wants the 3 best sellers for the window, and the 3 worst to think about dropping. Two products sold 142. That tie matters later.
| Row | A | B |
|---|---|---|
| 1 | Sales, March | |
| 2 | Product | Sold |
| 3 | Croissant | 180 |
| 4 | Sourdough | 142 |
| 5 | Bagel | 96 |
| 6 | Scone | 41 |
| 7 | Baguette | 118 |
| 8 | Cinnamon roll | 142 |
| 9 | Muffin | 75 |
| 10 | Flapjack | 58 |
The top 3 and the bottom 3
=LARGE(B3:B10,1) gives 180.
=LARGE(B3:B10,2) gives 142.
=LARGE(B3:B10,3) gives 142.
=SMALL(B3:B10,1) gives 41.
=SMALL(B3:B10,2) gives 58.
=SMALL(B3:B10,3) gives 75.
The second number is the position. With 1, LARGE is the same as MAX and SMALL is the same as
MIN. =MAX(B3:B10) gives 180 and
=MIN(B3:B10) gives 41.
Type 1, 2 and 3 in G3 to G5. In H3, type =LARGE($B$3:$B$10,G3)
and fill it down. Now one formula gives all 3.
To add up the top 3, add the three LARGEs:
=LARGE(B3:B10,1)+LARGE(B3:B10,2)+LARGE(B3:B10,3) gives 464.
Show who each number belongs to
LARGE gives a number, not a name. To get the product, look the number up in column B and return column A.
=INDEX(A3:A10,MATCH(LARGE(B3:B10,1),B3:B10,0)) gives Croissant.
This works in every version of Excel.
=XLOOKUP(LARGE(B3:B10,1),B3:B10,A3:A10) gives the same. XLOOKUP
needs Excel 2021 or later, or Microsoft 365.
For the bottom 3, put SMALL in place of LARGE. The names are Scone, Flapjack and Muffin. The INDEX and MATCH article explains how the lookup part works.
Ties: what LARGE returns, and what the lookup returns
Sourdough and Cinnamon roll both sold 142. LARGE counts entries, not different numbers. So 142 is second and also third. Both formulas give 142, and that is correct.
The problem is the name. Look up 142 and you get the first row with 142 in it. That is Sourdough, for position 2 and for position 3.
=INDEX(A3:A10,MATCH(LARGE(B3:B10,3),B3:B10,0)) gives Sourdough.
So does the XLOOKUP version. Your top 3 list shows Croissant, Sourdough, Sourdough.
Cinnamon roll is missing, and there is no error to tell you.
There are two good fixes. Which one depends on your Excel.
Fix 1: FILTER, in Excel 2021 and Microsoft 365
Ask for every row whose number is at least the third largest. Type this in D3, with empty cells below and to the right:
=FILTER(A3:B10,B3:B10>=LARGE(B3:B10,3)) fills D3 to E5:
| Croissant | 180 |
| Sourdough | 142 |
| Cinnamon roll | 142 |
Both products on 142 are there. FILTER keeps the order of the table.
Now say Baguette also sold 142. Then 3 products tie for second place, and FILTER returns 4 rows:
| Croissant | 180 |
| Sourdough | 142 |
| Baguette | 142 |
| Cinnamon roll | 142 |
For a report, that is usually the right answer. It is not fair to drop one of 3 equal products.
To put the largest first, wrap it: =SORT(FILTER(...),2,-1).
Fix 2: a helper column, in every version
Give every row its own position, with no two the same. In C3, type this and fill it down to C10:
=COUNTIF($B$3:$B$10,">"&B3)+COUNTIF($B$3:B3,B3)
The first COUNTIF counts the numbers bigger than this one. The second counts this number from the top down to this row. So the first 142 gets 2 and the second 142 gets 3.
Column C now reads 1, 2, 5, 8, 4, 3, 6, 7, from row 3 to row 10. No position appears twice.
Now look up the position, not the number. With 1, 2 and 3 in E3 to E5, type this in F3 and fill it down:
=INDEX($A$3:$A$10,MATCH(E3,$C$3:$C$10,0)) gives Croissant,
Sourdough and Cinnamon roll.
With 8, 7 and 6 in E3 to E5, the same formula gives the bottom 3: Scone, Flapjack, Muffin.
A tie goes to the row that comes first. That is a choice, so say it in the report. The half-locked
range $B$3:B3 is the same trick as in a
running total.
When "second" means the next different number
Sometimes you want the second-highest different number: 180, then 142, then 118. LARGE alone cannot do that, because it counts both 142s.
In Excel 2021 and Microsoft 365, remove the repeats first.
=LARGE(UNIQUE(B3:B10),2) gives 142.
=LARGE(UNIQUE(B3:B10),3) gives 118.
Decide which meaning you want before you build the report. "The 3 best products" and "the 3 best sales numbers" are different lists.
Questions people ask
Why does LARGE show #NUM!?
The position is bigger than the count of numbers, or it is 0. Our range has 8 numbers, so
=LARGE(B3:B10,9) gives #NUM!.
Check that the range covers every row.
Can MAX tell me which row has the largest number?
No. MAX gives the number only. Use the INDEX and MATCH lookup above to get the name beside it.
Should I sort the table instead?
Sorting works once. But next month the numbers change and you sort again. The formulas update by themselves, and the table can stay in the order you like.
You can practise LARGE, SMALL, MAX and MIN in the lessons below. Each lesson is a real sheet you type into, and each answer is checked when you press Enter.