Handle duplicate rankings in spreadsheet leaderboard using RANK and MATCH functions

Other Questions Google Sheets
1

I’m working on a player ranking system in Google Sheets where I calculate ranks based on wins and other match statistics. The problem comes up when multiple players have identical ranks.

I’m using RANK and MATCH functions to create a sorted leaderboard table from my player data. Everything works fine until I get players with the same ranking position.

What I want to achieve:

  • Show tied players with equal rank numbers like this:
1= John
1= Sarah  
3  Mike
4  Lisa
  • Also need to display players with 0 rank (haven’t played yet) at the bottom of the table in any order.

Right now my formula only picks up the first player when there are ties and ignores the second one. How can I modify my RANK/MATCH approach to handle these duplicate rank values and show all players properly in the leaderboard?

2

Had this exact problem with our gaming tournament tracker. Regular ranking formulas fall apart when you’ve got ties and need to show everyone. Here’s what worked: compound sorting. Sort by rank first (highest to lowest), then alphabetically by player name for the tiebreaker. All tied players stay grouped together but you get consistent ordering. For zero-rank players, treat them separately. Filter them out, then stick them at the bottom after your ranked list. The trick is pulling two separate lists - ranked players and unranked ones - then combining them. Way better than helper columns when you’re dealing with tons of players.

3

Yeah, MATCH always grabs the first identical value it finds - that’s why you’re only getting one result for tied ranks. Skip trying to force RANK/MATCH to work and use COUNTIFS instead. Make a helper column that combines your rank with a tie-breaker number. Use COUNTIFS to count how many times each rank appears, then number the duplicates (1st tie, 2nd tie, etc). Your main formula can pull from this helper column and grab all players, even the tied ones. For players with zero ranks, just add a condition in your sorting formula to dump them at the bottom. Works way better than wrestling with MATCH to find duplicate values, especially if you’ve got a big dataset.

4

Had this same problem last month! MATCH only grabs the first occurrence, so it misses duplicate ranks. Use SMALL instead of RANK/MATCH - handles ties much better. Try =INDEX(names,MATCH(SMALL(ranks,ROW()),ranks,0)) but you’ll need helper columns for ties. Way easier than wrestling with RANK.