Excel 365: Fix Top-N Lists That Duplicate a Name When Scores Tie

Picture this: you build a “Top 5 Sales Reps” report with the classic LARGE + MATCH combo, send it out, and someone replies “hey, why am I not on here? I know I beat Dana this month.” You check the raw numbers — they’re right, they tied with a teammate for third place. Your report doesn’t show them twice, and it doesn’t show an error. It just quietly shows their teammate’s name twice instead, and their name never appears anywhere. That’s not a data problem. That’s MATCH doing exactly what it’s documented to do — and it’s almost never what you want.

Who this quietly bites

This hits any ranked Top-N report built by pulling the k-th largest value and looking up the name that goes with it — which is most Top-N reports:

• Sales leaderboards — Top 5 or Top 10 reps by revenue, especially once numbers get rounded to the nearest hundred or thousand and ties get more likely.
• Product performance reports — Top-selling SKUs by units moved, where round numbers (50 units, 100 units) tie constantly.
• Academic rankings — class rank or top scorers when test scores are whole numbers out of 100.
• Competition and awards judging — top finalists by score, where a genuine tie for the last qualifying spot is common and consequential.
• Support/ops dashboards — “busiest agents this week” by ticket count, where round ticket counts tie often.

The more your underlying numbers are rounded, counted, or capped, the more often this bites — and it bites exactly where it matters most: right at the cutoff line of your Top N, where a tie decides who makes the list and who doesn’t.

Why LARGE + MATCH gets it wrong when scores tie

The standard Top-N formula looks like this, run for k = 1 through 5:

=INDEX(NameRange, MATCH(LARGE(ScoreRange, k), ScoreRange, 0))

LARGE(ScoreRange, k) correctly returns the k-th largest value — ties and all. The problem is the next step: MATCH doesn’t know anything about k. It just searches for that value and returns the position of the first cell that equals it. So when two people tie for 3rd place, LARGE(..., 3) and LARGE(..., 4) both return the same number, and MATCH resolves both lookups to the very same row — the first person in your data with that value.

Here’s a real leaderboard with one tie, entered in the order reps normally appear in a CRM export:

Run the naive formula for k = 1 to 5, and you get: Priya, Marcus, Sofia, Sofia, Wanjiru. Sofia shows up twice. Derek — who earned exactly as much as Sofia and rightfully belongs in the Top 5 — is nowhere on the report. Nothing errors. Nothing looks broken. It’s just quietly, confidently wrong, and it’ll stay that way until the person who got erased happens to check.

The fix, two ways

The fix in both cases is the same idea: stop asking “which row has this value?” (ambiguous when ties exist) and start asking “which row has this exact position?” (never ambiguous).

Option 1 — classic: a tie-broken rank column (Excel 2003 and up)

This uses one of the genuinely famous old Excel tricks — pairing RANK with a running COUNTIF to turn tied ranks into unique ones, without touching the displayed values at all.

1. Add a helper column next to your data that gives every row a unique rank:

C2  =RANK(B2, $B$2:$B$16) + COUNTIF($B$2:B2, B2) - 1

Fill down. RANK alone would give both Sofia and Derek a rank of 3 (standard competition ranking correctly skips to 5 for the next person). The COUNTIF($B$2:B2, B2) part counts how many times this value has appeared so far, top to bottom — 1 for whoever’s tied value shows up first, 2 for the next occurrence, and so on. Add that (minus 1) to the tied rank, and the first occurrence keeps rank 3, the second occurrence becomes rank 4 — genuinely unique, with no gaps, and the row order in your source data quietly acts as the tiebreaker.

2. Extract the Top 5 by exact rank position, not by value:

Name (1st):     =INDEX($A$2:$A$16, MATCH(1, $C$2:$C$16, 0))
Revenue (1st):  =INDEX($B$2:$B$16, MATCH(1, $C$2:$C$16, 0))

Copy down for ranks 2, 3, 4, 5 (just change the 1 to 2, 3, 4, 5). Since every row’s helper rank is now unique, MATCH can never land on the wrong row again — for our example, this correctly returns Priya, Marcus, Sofia, Derek, Wanjiru, in that order, no duplicates, nobody missing.

Option 2 — Excel 365 spill: SORTBY + TAKE sidesteps the whole bug

Here’s the deeper fix available on 365: SORTBY sorts entire rows, not values in isolation — so it never has to “find the row that matches this number” in the first place, which is the exact step that broke in Option 1’s naive version. One formula:

=TAKE(SORTBY(HSTACK(A2:A16, B2:B16), B2:B16, -1), 5)

Read it inside-out: HSTACK(A2:A16, B2:B16) glues the name and revenue columns together into one two-column array. SORTBY(..., B2:B16, -1) sorts that array by revenue, descending — and because it’s sorting whole rows, a tie between Sofia and Derek just keeps them in their original relative order (a stable sort) instead of collapsing them into the same lookup result. TAKE(..., 5) keeps the first 5 rows. Drop this in one cell and it spills a complete 5×2 Top-5 table — names and revenue together, ties handled correctly, with zero helper columns.

Priya Nair      $68,000
Marcus Webb     $61,500
Sofia Almeida   $54,000
Derek Osei      $54,000
Wanjiru Kamau   $49,000

Same correct result as the classic version — but structurally immune to the bug rather than patched around it, since SORTBY was never asking “which row has this value?” to begin with.

Worth knowing: if your cutoff lands mid-tie — say a Top 3 report with a three-way tie for 3rd place — both methods above will consistently pick whoever comes first in your source data (that’s what the COUNTIF tiebreaker and SORTBY’s stable sort both fall back on). That’s a legitimate business call to make explicitly, not a formula bug: if ties at the cutoff should be broken by date, ID, or a secondary metric instead, sort your source data by that tiebreaker before either formula runs.

Where else this pattern pays for itself

• “Bottom N” worst-performer reports — the identical bug, just with SMALL instead of LARGE.
• Award/prize allocation — top finishers who tie both deserve recognition; the naive formula quietly picks a “winner” between them.
• Class rank / GPA rankings — whole or rounded scores tie constantly; RANK + COUNTIF is the standard fix taught in gradebook spreadsheets for exactly this reason.
• Inventory “top movers” reports — unit counts are integers, so exact ties are the norm, not the exception, at any reasonable Top-N cutoff.
• Any leaderboard refreshed automatically — the bug is invisible until a tie happens to land near the cutoff, so it can pass review for months before it silently drops someone.

Download the workbook

The workbook has 15 sales reps with one deliberate tie at $54,000, positioned right at the edge of a Top 5 cutoff — exactly where this bug does the most damage. It shows the buggy naive LARGE+MATCH version, the fixed classic RANK+COUNTIF version, and the 365 SORTBY+TAKE version side by side on one sheet, so you can watch the buggy column duplicate Sofia and lose Derek while the other two get it right.

Download the top-N-ties workbook (.xlsx)

Takeaways

• LARGE + MATCH finds the first row matching a value — when two scores tie, both LARGE calls return that same value, so MATCH sends both lookups to the same row, duplicating one person and erasing another.
• The fix is to stop matching on the value and start matching on a guaranteed-unique position instead.
• Classic (Excel 2003+): =RANK(value, range) + COUNTIF($start:cell, value) - 1 turns tied ranks into unique, gap-free ranks; extract with INDEX/MATCH against that helper column.
• Excel 365: =TAKE(SORTBY(HSTACK(names, values), values, -1), 5) sorts whole rows instead of matching on values, so ties never collide in the first place — one formula, no helper column.
• If a tie lands exactly on your Top-N cutoff, both methods break the tie using row order — make that an intentional choice (sort by a secondary tiebreaker first) rather than an accident.