How to sort one column by another with a formula in Excel
Excel formula
=SORTBY(A2:A9,B2:B9,-1)Use SORTBY: =SORTBY(A2:A9,B2:B9,-1) returns the names in A ordered by the values in B, largest first. The sort column does not have to be in the output.
- Result on the example
- a 8-row list that spills from D2 in
D2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Not available, see below
See it work
The formula is already in D2. Change any input and the result updates, or edit the formula to experiment.
How it works
SORTBY(array, by_array, order) orders the first range by the values in the second. Order -1 is descending, 1 ascending. Unlike SORT, the sorting column does not have to be part of the result, and you can add more sort keys.
The result spills down from the cell you type in, so leave the cells below empty.
=SORTBY(A2:A9,B2:B9,-1)Result{8 rows × 1 col} "Marcus", "Lars", "Tom", "Hana", …
A2:A98 rows × 1 col: "Priya", "Tom", "Ines", "Marcus", …A range: 8 cells in one column.
B2:B98 rows × 1 col: $48,200, $61,500, $39,800, $72,300, …A range: 8 cells in one column.
-1-1Makes the value negative.
SORTBY(A2:A9,B2:B9,-1){8 rows × 1 col} "Marcus", "Lars", "Tom", "Hana", …SORTBY: Sorts a range by other ranges. Spills.- array
A2:A98 rows × 1 col: "Priya", "Tom", "Ines", "Marcus", … - by_array1
B2:B98 rows × 1 col: $48,200, $61,500, $39,800, $72,300, … - sort_order1
-1-1
- array
In Google Sheets
=SORT(A2:A9,B2:B9,FALSE), which sorts by another column the same way.Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(A2:A9,MATCH(LARGE(B2:B9,SEQUENCE(8)),B2:B9,0)) | spills 8 × 1Different result | Works without SORTBY, but breaks on tied values. Repeats a name when two reps have the same sales. |
Common mistakes
- A #SPILL! error means something is in the way of the results. Clear the cells below.
- The two ranges must be the same height.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Sort one column by another with a formula
D2, list the reps ranked by sales, highest first.Questions
How do I sort by two columns?
Add another pair: =SORTBY(A2:C9,B2:B9,1,C2:C9,-1).
Checked by running on 2026-11-20: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.