What SORTBY does
SORTBY returns a sorted copy of your data using one or more corresponding ranges as sort keys. You can display just the names while sorting by age, or return whole rows so each person's details stay together. The source data stays in place.
Practical examples
Use this data in A1:C5 for all three examples. Row 1 contains headers; the formulas sort only rows 2–5. Try each formula separately in E2, with E2:G5 empty.
| Row | A: Name | B: Region | C: Age |
|---|---|---|---|
| 1 | Name | Region | Age |
| 2 | Ana | West | 28 |
| 3 | Ben | East | 40 |
| 4 | Cara | West | 35 |
| 5 | Dan | East | 32 |
Sort names by age
=SORTBY(A2:A5,C2:C5)
The result spills into E2:E5:
| E |
|---|
| Ana |
| Dan |
| Cara |
| Ben |
The ages run from 28 to 40 because omitting sort_order1 gives ascending order. Column C supplies the order even though you return only the names in column A.
Keep whole rows together in descending order
=SORTBY(A2:C5,C2:C5,-1)
The result spills into E2:G5:
| E | F | G |
|---|---|---|
| Ben | East | 40 |
| Cara | West | 35 |
| Dan | East | 32 |
| Ana | West | 28 |
Here, -1 puts the oldest person first. Returning all three columns keeps each name, region, and age on the same row.
Group by region, then sort by age
=SORTBY(A2:C5,B2:B5,1,C2:C5,-1)
The result spills into E2:G5:
| E | F | G |
|---|---|---|
| Ben | East | 40 |
| Dan | East | 32 |
| Cara | West | 35 |
| Ana | West | 28 |
The first key puts East before West. The second key sorts ages from highest to lowest within each region, so Dan stays above Cara despite being younger.
Common mistakes and notes
Match the sort keys to the rows or columns
Each by_array must be a single column or a single row. To sort rows, supply one key value per source row: A2:C5 has four rows, so C2:C5 is a valid four-row key. The key doesn't need the source array's three columns. To sort columns, use a single-row key with one value per source column. Additional keys must match the same direction and length.
Keep headers out of both the data and the sort keys. In these examples, mixing A2:C5 with C1:C5 gives mismatched lengths.
Leave room for the result
SORTBY spills into neighboring cells. A blocked destination can produce #SPILL!; clear the cells needed for the output or move the formula. The examples returning three columns need all of E2:G5 available.
Use 1 or -1 for the sort order
Use 1 for ascending or -1 for descending. Other sort-order values produce #VALUE!. Add another sort key when ties need a specific order, as in the region-and-age example.
Keep linked source workbooks open
Dynamic arrays have limited support across workbooks. If SORTBY refers to another workbook, keep both workbooks open; refreshing the formula after closing the source workbook returns #REF!.