Ranking data
8 minutes · 5 steps · free, no sign-up
Who came first? What is the third best score? Ranking questions come up in sales leagues, test results and sports tables. You can answer them without sorting your data, so the original order stays as it is.
- Give every row a position with RANK
- See how ties share a rank
- Find the nth highest or lowest value with LARGE and SMALL
- Name the winner with INDEX and MATCH
Click a cell and start typing. Enter edits, Tab moves right, arrows move, Delete clears.
Quick referenceQuick reference
=RANK(B2, $B$2:$B$6) 1 = highest
=RANK(B2, $B$2:$B$6, 1) 1 = lowest
=LARGE(B2:B6, 3) the 3rd highest value
=SMALL(B2:B6, 2) the 2nd lowest value
=INDEX(A2:A6, MATCH(MAX(B2:B6), B2:B6, 0)) name of the top scorerThe key ideasSummary: Ranking data
RANK tells you where a value would sit if the list were sorted, without moving anything. By default, 1 is the highest. Lock the range with dollar signs so it can be filled down. When two values tie they share a rank and the next rank is skipped: two players on 92 are both first, and the next score is fourth, not third.
LARGE and SMALL return the value at a position rather than the position of a value. To get the name of whoever has a value, find its row with MATCH and read the name with INDEX.
Watch out forCommon mistakes
- Not locking the range.
RANK(B2, B2:B6)filled down becomesB3:B7. Use$B$2:$B$6. - Expecting 1, 2, 3 with ties. Tied values share a rank, and the next one is skipped.
- Asking for a position that does not exist. LARGE(B2:B6, 9) on five values gives
#NUM!. - Trying to find a name with LARGE. LARGE gives a number. Use INDEX and MATCH to look up the name next to it.
Next stepTry it in the real Google Sheets
Newer Google Sheets and Excel also have SORT and FILTER, which can build a whole leaderboard from one formula. They are covered in the Adept level.