Excel track · Exercise 81 of 86 Paid
SORT and spilled ranges
Rank a table without touching it, then read one cell out of the ranked block.
Advanced
Sales rep scoreboard
Read-only data · answer in B8
In B8, return the name of the rep with the highest score from A2:B7, ranking the table with SORT before you pick a cell.
| A | B | |
|---|---|---|
| 1 | Rep | Score |
| 2 | Ada | 88 |
| 3 | Mia | 95 |
| 4 | Oz | 72 |
| 5 | Ivy | 91 |
| 6 | Sam | 61 |
| 7 | Lena | 80 |
| 8 | Top scorer |
Reference answer
What it does
SORT returns a new block of rows in the order you ask for, and the source table keeps its own order. The second argument names the column inside that array, so 2 is the score column. The third takes 1 for ascending and -1 for descending.
The six reps hold scores of 88, 95, 72, 91, 61 and 80. Sorting on column 2 descending puts Mia's 95 on the first row and Sam's 61 last. On its own, SORT spills six rows starting at B8, so =INDEX(SORT(A2:B7,2,-1),1,1) asks the ranked block for row 1, column 1: the name at the top.
Change the 1 to 2 and the same formula reads the runner-up, Ivy on 91. The sort index counts columns inside the array, not columns on the sheet, so 3 on this two-column table returns #VALUE!.
Syntax
=SORT(array, [sort_index], [sort_order], [by_col])Worked example
On Sales rep scoreboard, =INDEX(SORT(A2:B7,2,-1),1,1) returns Mia.
=INDEX(SORT(A2:B7,2,-1),1,1)Common errors
| Error | What it means and how to fix it |
|---|---|
#VALUE! | The sort index is outside the array, or INDEX was given a row without a column. |
#REF! | INDEX asked for a row past the sixth, which the sorted block does not have. |
#NAME? | The file is open in an Excel without dynamic arrays, so SORT is not recognised. |
At work
Top customer by revenue, and the latest invoice date, both read from a ranked copy of a small table.