Lesson 29 of 45, Dynamic arrays
SORTBY, TAKE & SEQUENCE
Quick answer
Build a top-N leaderboard that re-ranks itself.
- Key formula
=SORTBY(A2:A11,C2:C11,-1)(Names ranked by revenue, largest first)
Watch it done
2 stepsWatch the technique worked out on sample data, then try it yourself in the exercises.
How it works
SORT orders a table by one of its own columns. SORTBY(array, by_array, order) is more flexible: it orders any list by another range, so you can return just the names, ranked by a revenue column that is not part of the result.
TAKE(array, rows) keeps the first rows of a list (a negative number keeps the last). Put it around SORTBY for a top 3 or bottom 5 that updates when the data changes.
SEQUENCE(n) returns the numbers 1 to n down a column: perfect for a rank column beside the names.
Syntax
=SORTBY(A2:A11,C2:C11,-1)Names ranked by revenue, largest first=TAKE(SORTBY(A2:C11,C2:C11,-1),3)Top 3 rows of the table=SEQUENCE(5)1, 2, 3, 4, 5 down a column=TAKE(SORTBY(A2:A11,C2:C11,1),3)Three lowest, smallest firstWatch out for
- The sort key must be the same height as the array: A2:A11 sorted by C2:C11, not C2:C12.
- TAKE and SEQUENCE spill. Keep the cells beneath and beside the formula empty, or you get #SPILL!.
- Ties keep their original order. If rank matters, add a tie-breaker as a second key.
The exercises
- Names by revenue. In E2, list the reps (column A) ranked by revenue, largest first. Use one formula that spills.
- Top three. In G2, return the top 3 reps with their region and revenue (the whole row of A2:C11), ranked by revenue.
- Numbered top five. In K2, number the ranks 1 to 5 with SEQUENCE. In L2, list the top five rep names, ranked by revenue.
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.
- Names by revenue
- Top three
- Numbered top five