Lookup & Reference
Rank & Score Leaderboard with Deterministic Tie-Breaking (RANK + INDEX/MATCH)
Build a live 1-through-N ranked leaderboard that resolves tied scores deterministically via RANK + COUNTIF running offsets so INDEX and MATCH never repeat the same contender.
Why plain RANK + INDEX/MATCH breaks when two contenders tie
RANK(B2, $B$2:$B$6) assigns identical rank integers to tied scores—for example, two players tied at 92 both receive rank 2, and rank 3 is skipped entirely. When a leaderboard table searches for rank 2 with MATCH(2, $C$2:$C$6, 0), it returns the first tied player, and searching for rank 3 fails with #N/A!
Adding an expanding occurrence offset COUNTIF($B$2:B2, B2) - 1 to RANK(B2, $B$2:$B$6) produces a strictly bijective permutation of 1..N (e.g., 2 and 3 for the two tied 92 scores, in top-to-bottom entry order). Every integer k from 1 to N then resolves cleanly with INDEX + MATCH.
| Contender | Raw Score | Standard RANK | Unique Rank (RANK + COUNTIF - 1) |
|---|---|---|---|
| Aria | 88 | 4 | 4 |
| Devon | 92 | 2 | 2 |
| Kiran | 97 | 1 | 1 |
| Mateo | 92 | 2 (Tie!) | 3 (Disambiguated) |
| Sora | 81 | 5 | 5 |
Unique 1..N Tie-Broken Rank Helper Formula
Lock the full score range in RANK(B2, $B$2:$B$6) and anchor only the top cell in COUNTIF($B$2:B2, B2) - 1 so duplicate scores step sequentially by +1.
=RANK(B2, $B$2:$B$6) + COUNTIF($B$2:B2, B2) - 1Leaderboard Name & Score Lookup by Slot k
Look up each leaderboard position E2 (1, 2, 3, ...) against the unique rank column $C$2:$C$6 with INDEX + MATCH.
=INDEX($A$2:$A$6, MATCH(E2, $C$2:$C$6, 0))Try the example
Use the original values or change them to test your understanding.