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 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. Grab a coffee — we’ll fix it two ways, and add the one check that would have caught it before it went out.

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 by revenue, especially once numbers get rounded to the nearest hundred or thousand and ties get likelier.
• Product performance reports — top-selling SKUs by units moved, where round numbers tie constantly.
• Academic rankings — class rank or top scorers when 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 and ops dashboards — “busiest agents this week” by ticket count, where integer 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, 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 searches for that value and returns the position of the first cell that equals it.

So when two people tie for 3rd, LARGE(...,3) and LARGE(...,4) both return the same number, and MATCH resolves both lookups to the very same row.

Here’s the leaderboard as it actually arrives from a CRM export — unsorted, in whatever order the system felt like:

Run the naive formula for k = 1 to 5 and you get: Priya, Marcus, Sofia, Sofia, Wanjiru.

Sofia appears 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.

The fix, two ways

The scenario, in full. Fifteen sales reps in A6:A20, revenue in B6:B20, one deliberate tie at $54,000 sitting exactly on the Top 5 cutoff. A Top_N input cell so the cutoff can be moved.

The fix in both cases is the same idea: stop asking “which row has this value?” — ambiguous the moment two rows share it — and start asking “which row has this exact position?”, which never is.

Option 1 — Classic (backward-compatible functions, step by step)

Step 1 — Give every row a unique rank.

C6  =RANK(B6, $B$6:$B$20) + COUNTIF($B$6:B6, B6) - 1

Fill down. RANK alone gives both Sofia and Derek a 3 — standard competition ranking, which correctly skips to 5 for the next person.

The COUNTIF($B$6:B6, B6) part counts how many times this value has appeared so far, top to bottom: 1 for the first occurrence, 2 for the next. Add that minus one to the tied rank and the first occurrence keeps rank 3, the second becomes rank 4 — unique, gap-free, with source row order acting as the tiebreaker.

Note the half-anchored range. $B$6 is pinned; B6 grows as you fill down. That expanding window is the entire mechanism, and it’s the part people get wrong when they retype it from memory.

Step 2 — Extract by exact rank position, not by value.

Name:     =INDEX($A$6:$A$20, MATCH(E6, $C$6:$C$20, 0))
Revenue:  =INDEX($B$6:$B$20, MATCH(E6, $C$6:$C$20, 0))

where E6 holds the k you want — 1, 2, 3, and so on. 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 (dynamic-array equivalent)

Here’s the deeper fix. SORTBY sorts entire rows, not values in isolation — so it never has to find the row that matches a number, which is the exact step that broke. One formula:

=TAKE(SORTBY(HSTACK($A$6:$A$20, $B$6:$B$20), $B$6:$B$20, -1), Top_N)

Read it inside-out. HSTACK glues the name and revenue columns into one two-column array. SORTBY(..., revenue, -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 rather than collapsing into the same lookup result. TAKE(..., Top_N) keeps the first N rows.

Drop it in one cell and it spills a complete Top-5 table, names and revenue together, ties handled correctly, 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 rather than patched, because SORTBY was never asking “which row has this value?” to begin with.

The check that catches it before it ships

Whichever version you use, put this somewhere on the sheet:

=SUMPRODUCT((TopNames<>"")/COUNTIF(TopNames, TopNames&""))

It counts distinct non-blank names. If that number is less than your N, the report has erased somebody. It’s one cell, it costs nothing, and it’s the only check that catches this bug — because, as above, the totals won’t.

Where else this pattern pays for itself

• “Bottom N” worst-performer reports — the identical bug, just with SMALL instead of LARGE, and with higher stakes attached to being on the list.
• Award and prize allocation — top finishers who tie both deserve recognition; the naive formula quietly picks a winner between them.
• Class rank and GPA rankings — whole or rounded scores tie constantly, which is why RANK + COUNTIF is the standard fix taught in gradebook sheets.
• Inventory “top movers” — unit counts are integers, so exact ties are the norm rather than the exception at any reasonable 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

Fifteen 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.

Three sheets: Leaderboard shows all three methods side by side on one sheet — the buggy naive LARGE+MATCH, the fixed classic RANK+COUNTIF, and the 365 SORTBY+TAKE — so you can watch the pink column duplicate Sofia and lose Derek while the other two get it right. A Top N input drives all three, and a distinct-name count under each block is the tell in one number. Check counts distinct names per method, names whoever the naive version dropped, proves the classic and 365 versions agree row for row, and totals the revenue of each list so you can see for yourself that the buggy one adds up perfectly.

Push Top N to 3 and the cutoff lands mid-tie, which is the case worth understanding before it happens to a real report.

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

The SORTBY + TAKE column needs Microsoft 365 or Excel 2021+. Everything else opens in Excel 2003 onwards.

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 totals still tie. LARGE returned the right five values; only the names are wrong, so every plausible sanity check comes back clean.
• The fix is to stop matching on the value and start matching on a guaranteed-unique position.
• Classic (Excel 2003+): =RANK(value, range) + COUNTIF($start:cell, value) - 1 turns tied ranks into unique, gap-free ones. The half-anchored $B$6:B6 window is the mechanism.
• Excel 365: =TAKE(SORTBY(HSTACK(names, values), values, -1), N) sorts whole rows, so ties never collide. SORTBY’s stable sort is a documented guarantee, not luck.
• Count distinct names. =SUMPRODUCT((names<>"")/COUNTIF(names, names&"")) is the only check that catches this, and it’s one cell.
• Your move: find the Top-N report you send out most often and count the distinct names in it. If it’s ever fewer than N, somebody has been missing from it.