Array FunctionsIntermediate

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.

Interactive Spreadsheet

Read about SORT

Hints

+5 XPno-hints bonus

Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.

Variations of SORT

0/3

The same formula in the other shapes it takes at work.

  1. Order one column by the values in another

    SORTBY sorts a range using a separate range as the key.

    CoreIntermediate
  2. SORTBY ascending: most urgent first

    Order a list by a priority number that is not shown.

    Intermediate
  3. SORTBY returning two columns

    Return names and teams ordered by a score you keep private.

    Intermediate

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.

7 rows × 5 columns4 cells you fill in
ABCDE
1RepSalesBy performance
2Ada4200
3Bo3100
4Cy5800
5Dee2400
6Eve4900
7Fin3600
What this exercise teachesMay contain 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.