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.
| Rep | Revenue | Bonus paid | Bonus owed |
|---|---|---|---|
| Okafor | 184,250 | 6,000 | 6,000 |
| Brennan | 171,900 | 12,000 | 6,000 |
| Lindqvist | 171,900 | 0 | 6,000 |
Scope. The
LARGE,INDEX,MATCH,COUNTIFandSUMPRODUCTformulas work in every version of Excel.RANK.EQneeds Excel 2010 or later; useRANKbefore that.
SORTandFILTERneed Excel 2021 or Microsoft 365, andTAKEneeds 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.
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.
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.
| Place | LARGE | MATCH position | Name |
|---|---|---|---|
| 1 | 184,250 | 4 | Okafor |
| 2 | 171,900 | 2 | Brennan |
| 3 | 171,900 | 2 | Brennan |
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.
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.
| Rep | Revenue | RANK.EQ | COUNTIF so far | Unique rank |
|---|---|---|---|---|
| Okafor | 184,250 | 1 | 1 | 1 |
| Brennan | 171,900 | 2 | 1 | 2 |
| Lindqvist | 171,900 | 2 | 2 | 3 |
| Marsh | 168,400 | 4 | 1 | 4 |
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.
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.
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:
| Policy | Formula | Names |
|---|---|---|
| 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.
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.
07Five Traps
- Looking up a value that two rows share.
LARGEwithINDEX/MATCH,XLOOKUPorVLOOKUPreturns the first row every time. Look up something unique. - Fixing it with a lookup that searches from the bottom.
XLOOKUPwith search mode -1 finds Lindqvist for place 3, and Lindqvist again for place 2. - Adding a tiny amount to break ties.
=B2+ROW()/1000000changes the revenue you report and still fails when the gaps are small. Rank, don't edit the money. - Using RANK.AVG for the tie-breaker. It gives tied reps 2.5 each, which no place number matches. Use
RANK.EQ. - Ranking a column that holds text numbers.
LARGEandRANK.EQskip revenue stored as text, so a rep can vanish from the list. Check with=COUNT(B2:B11).
08Four Exercises
- Reproduce it. Build the sheet and confirm that the
INDEX/MATCHlist reads Okafor, Brennan, Brennan. - Fix it in any version. Add the unique rank in column C and confirm that G4 returns Lindqvist.
- Fix it in Microsoft 365. Confirm that
=TAKE(SORT(A2:B11,2,-1),3)returns three different reps. - Move the tie. Set Marsh to 171,900 and compare the
TAKEandFILTERlists. 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.