01 / 09The Bonus List Named Brennan Twice

LookupsRANKStatistics

Top N With Ties in Excel: Fix LARGE and INDEX/MATCH Repeats

Two Reps Tied for Second, the Bonus List Named the Same One Twice, and the Other Lost $6,000, Because INDEX/MATCH Always Finds the First Match

10 Nov 20267 min read

UsesLARGE, SMALLINDEX MATCHRANKSORT

Halberd Office Supply pays its top three sales reps a $6,000 bonus each quarter. In Q3 two reps tied for second, and the bonus sheet named the same rep twice. One was paid $12,000 and the other nothing.

RepRevenueBonus paidBonus owed
Okafor184,2506,0006,000
Brennan171,90012,0006,000
Lindqvist171,90006,000

Scope. The LARGE, INDEX, MATCH, COUNTIF and SUMPRODUCT formulas work in every version of Excel. RANK.EQ needs Excel 2010 or later; use RANK before that.

SORT and FILTER need Excel 2021 or Microsoft 365, and TAKE needs Microsoft 365 or Excel 2024.

01The Bonus List Named Brennan Twice

The sheet lists ten reps in A2:B11, in the order the sales system exports them. The bonus list found each place's revenue first, then looked the name up. In E2, filled to E4:

=LARGE($B$2:$B$11,D2)

Q3 Revenue and the Top-Three Bonus List

Halberd Office Supply's ten reps in A1:G11, in the order the sales system exports them. Column C is a tie-broken rank. D:G is the bonus list: E finds each place's revenue with LARGE, F looks the name up with INDEX/MATCH, and G looks it up by the unique rank instead.

ABCDEFG
1
Rep
Revenue ($)
Unique rank
Place
LARGE
INDEX/MATCH
Tie-safe name
2
Chen
149750
6
1
184250
Okafor
Okafor
3
Brennan
171900
2
2
171900
Brennan
Brennan
4
Sato
118900
10
3
171900
Brennan
Lindqvist
5
Okafor
184250
1
6
Lindqvist
171900
3
7
Duarte
141200
7
8
Marsh
168400
4
9
Novak
133600
8
10
Haddad
152300
5
11
Pereira
127450
9

fxCells with formulas are highlighted in green

Hover over formula cells to see the formula and highlight referenced cells

LARGE is right on every row: 184,250, then 171,900 twice, because two reps really did sell $171,900. The name comes from F2, filled down:

=INDEX($A$2:$A$11,MATCH(E2,$B$2:$B$11,0))

F3 and F4 both say Brennan. Payroll paid the list as written, so Brennan got two bonuses and Lindqvist none. Nobody noticed until Lindqvist asked.

Scenario: Enter the ten reps and revenues in A2:B11, 1 to 3 in D2:D4, and the LARGE and INDEX/MATCH formulas in E2:F4. Check that F3 and F4 both return Brennan.

Try it in the grid

02Every Lookup Stops at the First Match

MATCH with 0 scans down from the top and returns the position of the first cell that matches. Brennan's 171,900 is in row 3 and Lindqvist's in row 6, so the scan stops at Brennan every time.

PlaceLARGEMATCH positionName
1184,2504Okafor
2171,9002Brennan
3171,9002Brennan

Swapping the lookup does not help. XLOOKUP(E4,$B$2:$B$11,$A$2:$A$11) and VLOOKUP also return the first match. The lookup is asked the same question twice and gives the same answer twice.

The values in column E cannot carry the difference, because a revenue says nothing about which row it came from. The fix has to give each row something unique to look up, or stop looking up altogether.

Scenario: Enter =MATCH(E4,$B$2:$B$11,0) in H4 and check that it returns 2. Then change B3 to 171,899 and watch F4 change to Lindqvist.

Try it in the grid

03Break the Tie With COUNTIF, in Any Version

Give every rep a rank that no one else shares. Count how many times the value has already appeared above and add it to the ordinary rank. In C2, filled to C11:

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

RANK.EQ gives both tied reps a 2. The COUNTIF range grows as it fills down, so it counts 1 for Brennan and 2 for Lindqvist. Brennan stays on 2 and Lindqvist moves to 3.

RepRevenueRANK.EQCOUNTIF so farUnique rank
Okafor184,250111
Brennan171,900212
Lindqvist171,900223
Marsh168,400414

Now look up the place in the rank column instead of the revenue. In G2, filled to G4:

=INDEX($A$2:$A$11,MATCH(D2,$C$2:$C$11,0))

It returns Okafor, Brennan and Lindqvist. The earlier row wins the tie, which is a rule, not an accident; section 5 covers when you want a different one.

Scenario: Enter the RANK.EQ and COUNTIF formula in C2 and fill it to C11. Check that C3 reads 2 and C6 reads 3, then enter the INDEX/MATCH formula in G2:G4.

Try it in the grid

04Sort the Rows in Microsoft 365

With dynamic arrays you can skip the lookup. Sort the whole table by revenue and keep the top three rows:

=TAKE(SORT(A2:B11,2,-1),3)

It spills three rows of name and revenue: Okafor, Brennan and Lindqvist. Each row of the result is a real row of the source, so a name can never appear twice unless it appears twice in the data.

SORT keeps tied rows in their source order, so Brennan comes before Lindqvist, the same rule as the COUNTIF tie-breaker. In Excel 2021, which has SORT but not TAKE, enter =SORT(A2:B11,2,-1) and read the first three rows.

To break ties on something you choose, such as fewer discounts, sort by two columns with SORTBY. The list then says why one tied rep came first.

Scenario: Enter =TAKE(SORT(A2:B11,2,-1),3) in I2. Check that it spills Okafor, Brennan and Lindqvist with their revenue, then change 3 to 4 and check that Marsh joins them.

Try it in the grid

05Decide What a Tie at the Cut-Off Means

A tie for second has an easy answer: both reps are in the top three. A tie for third is a policy question, and the formula should follow the policy, not set it.

Suppose Marsh had also sold $171,900. Three reps share second place, and only two places are left. The two fixes now disagree:

PolicyFormulaNames
Exactly three, earlier row wins=TAKE(SORT(A2:B11,2,-1),3)3
Everyone at or above third place=FILTER(A2:A11,B2:B11>=LARGE(B2:B11,3))4

The FILTER version pays everyone who reached the third-highest revenue. Its names come out in source order; to sort them, use =LET(s,SORT(A2:B11,2,-1),FILTER(s,INDEX(s,,2)>=LARGE(B2:B11,3))).

Neither list is wrong. A four-way bonus costs $24,000 against a $18,000 budget, and a three-way one leaves a rep with the same revenue unpaid. Write the rule down next to the list.

Scenario: Enter the FILTER formula in K2, then change B8 to 171900. Check that it spills four names while the TAKE formula still shows three.

Try it in the grid

06Check for Repeated Names Before It Goes Out

The broken list looked finished. A one-cell check would have caught it: count how many names on the list appear more than once.

=SUMPRODUCT(--(COUNTIF(F2:F4,F2:F4)>1))

On the broken list it returns 2, for the two Brennans. On the tie-safe list in G2:G4 it returns 0. In Microsoft 365, =ROWS(UNIQUE(F2:F4)) returns 2 when it should return 3.

Put the check beside the list and format it red when it is above 0. It costs one cell and works however the list was built.

Scenario: Enter the SUMPRODUCT check under the bonus list, pointed at F2:F4. Check that it returns 2, then point it at G2:G4 and check that it returns 0.

Try it in the grid

07Five Traps

  1. Looking up a value that two rows share. LARGE with INDEX/MATCH, XLOOKUP or VLOOKUP returns the first row every time. Look up something unique.
  2. Fixing it with a lookup that searches from the bottom. XLOOKUP with search mode -1 finds Lindqvist for place 3, and Lindqvist again for place 2.
  3. Adding a tiny amount to break ties. =B2+ROW()/1000000 changes the revenue you report and still fails when the gaps are small. Rank, don't edit the money.
  4. Using RANK.AVG for the tie-breaker. It gives tied reps 2.5 each, which no place number matches. Use RANK.EQ.
  5. Ranking a column that holds text numbers. LARGE and RANK.EQ skip revenue stored as text, so a rep can vanish from the list. Check with =COUNT(B2:B11).

08Four Exercises

  1. Reproduce it. Build the sheet and confirm that the INDEX/MATCH list reads Okafor, Brennan, Brennan.
  2. Fix it in any version. Add the unique rank in column C and confirm that G4 returns Lindqvist.
  3. Fix it in Microsoft 365. Confirm that =TAKE(SORT(A2:B11,2,-1),3) returns three different reps.
  4. Move the tie. Set Marsh to 171,900 and compare the TAKE and FILTER lists. Write down which one your bonus rule asks for.

What to Take Away

Every number on Halberd's bonus sheet was right. The error was asking a lookup to tell two equal numbers apart, which it cannot do.

When values can tie, give each row a unique rank to look up, or sort the rows themselves with SORT and TAKE. Decide what a tie at the cut-off means, and check the finished list for repeated names.

Share this article:
Back to Blog
Daily challenge · Day 107

Total a Mileage Claim for One Pay Period with SUMIFS

A new exercise every day, solved in a real grid.

Solve today’s challenge