Lesson 34 of 45, Analysis
RANK, LARGE & SMALL
Quick answer
Who's on top, and by how much.
- Key formula
=RANK.EQ(B2,$B$2:$B$9)(Rank, 1 = highest)
Watch it done
3 stepsWatch the technique worked out on sample data, then try it yourself in the exercises.
How it works
RANK.EQ(number, range) returns a value's place in a list: 1 is the largest. LARGE(range, k) returns the k-th largest value; SMALL the k-th smallest.
To get the name that goes with a top value, look it up: XLOOKUP(MAX(B2:B9),B2:B9,A2:A9).
Syntax
=RANK.EQ(B2,$B$2:$B$9)Rank, 1 = highest=LARGE($B$2:$B$9,E2)k-th largest=SMALL(B2:B9,1)Smallest=XLOOKUP(MAX(B2:B9),B2:B9,A2:A9)Name of the top valueWatch out for
- Lock the range in RANK, or the list shrinks as you fill down.
- Ties share a rank, and the next rank is skipped.
The exercises
- Rank the team. In C2, rank each rep's sales (1 = highest). Fill down to C9.
- Top three values. In F2, return the k-th largest sale using the k in E2. Fill down to F4.
- Name the leader. In H2, return the name of the rep with the highest sales.
Questions people ask about this
Updated Checked by running: every exercise answer on this page was graded by our formula engine on .
Graded practice for this lesson is part of Pro
The walkthrough and notes are open to everyone. Pro unlocks the exercises here and in the other 43 Pro lessons, Excel and Google Sheets, 125 exercises in all.
- Rank the team
- Top three values
- Name the leader