Statistical
Break ties in RANK: assign unique 1..N ranks with RANK.EQ and COUNTIF
Add COUNTIF($B$2:B2, B2) - 1 to RANK.EQ so tied scores receive sequential ranks 1, 2, 3, 4 for clean Top-N lookups.
Why tied ranks break Top-N INDEX/MATCH tables
When two reps tie at 90 points, RANK.EQ(B2, $B$2:$B$5) assigns rank 1 to BOTH reps and skips rank 2 completely (1, 3, 1, 4). A leaderboard looking up Rank = 2 then fails with #N/A, and Rank = 1 returns the same first rep twice!
Adding an expanding COUNTIF($B$2:B2, B2) - 1 adds +0 to the first occurrence of 90 and +1 to the second occurrence—producing unique ranks 1, 3, 2, 4.
| Rep (A) | Score (B) | Raw RANK.EQ | Unique Rank Formula |
|---|---|---|---|
| Aria | 90 | 1 | 1 (first 90 -> +0) |
| Bram | 85 | 3 | 3 |
| Cleo | 90 | 1 | 2 (second 90 -> +1) |
| Dion | 80 | 4 | 4 |
Compute unique descending rank
At row 4 (Cleo, second 90), RANK.EQ is 1 and COUNTIF($B$2:B4, B4) is 2, so 1 + 2 - 1 = 2.
This resolves ties by source row order. Sorting the source changes which tied record ranks first; use an explicit secondary key if that order must remain stable.
=RANK.EQ(B4, $B$2:$B$5) + COUNTIF($B$2:B4, B4) - 1Look up #2 ranked rep cleanly without #N/A
With unique ranks stored in C2:C5, XLOOKUP(2, C2:C5, A2:A5) retrieves "Cleo" with zero collision.
=XLOOKUP(2, C2:C5, A2:A5)Try the example
Use the original values or change them to test your understanding.