Array Functions
Intermediate
Order one column by the values in another
SORTBY sorts a range using a separate range as the key.
Task:
You want the rep names in order of sales, but only the names — the numbers are confidential and stay out of the report. In E2, use SORTBY to list the names from A2:A7 ordered by the sales in B2:B7, largest first.
Learning Objectives:
- Sort by a key held elsewhere
- Choose SORTBY over SORT deliberately
- Keep confidential columns out of a result
Hints
Solve without hints for +5 XP
The data in this exercise
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Rep | Sales | By performance | ||
| 2 | Ada | 4200 | |||
| 3 | Bo | 3100 | |||
| 4 | Cy | 5800 | |||
| 5 | Dee | 2400 | |||
| 6 | Eve | 4900 | |||
| 7 | Fin | 3600 |
What this exercise teaches (contains the answer)
This is the one people reach for SORT and then get stuck on, because SORT can only sort by a column inside the result. SORTBY separates what you rank by from what you show.