Top 10 list in Excel without SORT: LARGE + INDEX MATCH

The two formulas behind a top 10 list

LARGE(range,k) returns the k-th largest number in a range: k = 1 is the maximum, k = 2 the runner-up and so on. With the place numbers 1 to 10 in column A, one formula copied down returns the whole list of values: =LARGE($B$2:$B$13,A16). In the example that gives 2,310 cups for place 1 (Latte) down to 960 for place 10 (Espresso).

To show who has that value, look it up: =INDEX($A$2:$A$13,MATCH(B16,$B$2:$B$13,0)). MATCH finds the row of the value, INDEX returns the drink from that row. The 0 asks for an exact match, which is what you want here. If XLOOKUP is available, =XLOOKUP(B16,$B$2:$B$13,$A$2:$A$13) does the same, see XLOOKUP vs VLOOKUP.

Why ties break the simple version

MATCH always returns the first row that holds the value. Flat white and Cappuccino both sold 1,840 cups, so places 3 and 4 both show Flat white. Mocha, Chai latte and Matcha latte all sold 1,120 cups, so places 7, 8 and 9 all show Mocha. The values in column B are right, but four names in column C are wrong and two drinks vanish from the list. Nothing shows an error, which is why this bug often survives in real reports.

The fix: a unique rank with a COUNTIF tie-breaker

Give every row a different rank from 1 to 12 in a helper column:

=RANK.EQ(B2,$B$2:$B$13)+COUNTIF($B$2:B2,B2)-1

RANK.EQ gives tied values the same rank: all three 1,120 drinks get 7. The running COUNTIF($B$2:B2,B2) counts how often the value has appeared so far, from the top down to the current row. Note the mixed reference: the start $B$2 is fixed and the end moves as you copy down. The first 1,120 keeps 7, the second becomes 8 and the third 9. Ties are broken by the order of the rows, so sort the source or add a second criterion if the order matters.

Look up the place, not the value

With unique ranks you no longer search for the value at all. Look up the place number directly: =INDEX($A$2:$A$13,MATCH(A16,$C$2:$C$13,0)). Each place from 1 to 10 exists exactly once in the helper column, so every drink appears once and none is lost. Column D in the example shows the corrected list: Flat white and Cappuccino in places 3 and 4, then Mocha, Chai latte and Matcha latte in places 7 to 9. The values in column B stay as they are, because LARGE already handles ties correctly.

Worked example: top 10 drinks of a coffee shop in September

Rows 2 to 13 hold the cups sold per drink, with a unique rank in column C. Rows 16 to 25 are the top 10: the values from LARGE, the names from a lookup on the value (column C, wrong with ties) and the names from a lookup on the unique rank (column D, correct).

Worked example: top 10 drinks of a coffee shop in September

	A	B	C	D
1	Drink	Cups sold	Unique rank
2	Flat white	1,840	3
3	Latte	2,310	1
4	Cappuccino	1,840	4
5	Espresso	960	10
6	Americano	1,515	6
7	Mocha	1,120	7
8	Chai latte	1,120	8
9	Cold brew	1,690	5
10	Hot chocolate	875	11
11	Matcha latte	1,120	9
12	Iced latte	2,045	2
13	Cortado	640	12
14
15	Place	Cups sold	Drink (MATCH on value)	Drink (tie-safe)
16	1	2,310	Latte	Latte
17	2	2,045	Iced latte	Iced latte
18	3	1,840	Flat white	Flat white
19	4	1,840	Flat white	Cappuccino
20	5	1,690	Cold brew	Cold brew
21	6	1,515	Americano	Americano
22	7	1,120	Mocha	Mocha
23	8	1,120	Mocha	Chai latte
24	9	1,120	Mocha	Matcha latte
25	10	960	Espresso	Espresso

Fictional sales figures for practice. Copy the formulas down to row 13 (rank) and row 25 (top 10).

Bottom 10, top N from a cell and a condition

• Bottom 10: use SMALL instead of LARGE, and an ascending rank: =RANK.EQ(B2,$B$2:$B$13,1)+COUNTIF($B$2:B2,B2)-1.
• Top N set by the user: put N in a cell, say F1, and wrap the formulas: =IF(A16<=$F$1,LARGE($B$2:$B$13,A16),""). Prepare as many rows as the largest N you allow.
• Top 10 for one group only: add a helper column that returns the value only for matching rows, for example =IF(C2="North",B2,""). LARGE ignores text and empty strings, so it ranks only the North rows. To pull the matching rows themselves, see INDEX MATCH with multiple criteria.

In Excel 365: SORTBY and TAKE

With dynamic arrays a single formula spills the whole list: =TAKE(SORTBY(A2:B13,B2:B13,-1),10) sorts by cups sold in descending order and keeps the first ten rows, ties included. It is short, but it fails in Excel 2019 and older, in many company templates and in files shared with people on older versions. The LARGE + INDEX/MATCH pattern with a unique rank works everywhere, and it shows exactly how the ranking is built.

Common mistakes

• Looking up the value when there are ties. MATCH finds the first match only, so tied values repeat one name. Look up a unique rank instead.
• A fully absolute COUNTIF range. The tie-breaker needs $B$2:B2, a fixed start and a moving end. $B$2:$B$13 adds the same number to every tied row.
• Forgetting the 0 in MATCH. Without it MATCH does an approximate match on unsorted data and returns wrong names without an error.
• Relative ranges that shift when copied. Lock the data range with $ in LARGE, RANK.EQ and INDEX, or the list drifts down row by row.
• Numbers stored as text. LARGE and RANK.EQ ignore them, so a product can be missing from the list. Convert the column to numbers first.
• Hard-coding k. Typing 1, 2, 3 into ten separate LARGE formulas works, but a place column you refer to makes the list easy to extend and to drive with N from a cell.