Spreadsheet Practice

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", …

  1. A2:A98 rows × 1 col: "Priya", "Tom", "Ines", "Marcus", …

    A range: 8 cells in one column.

  2. B2:B98 rows × 1 col: $48,200, $61,500, $39,800, $72,300, …

    A range: 8 cells in one column.

  3. -1-1

    Makes the value negative.

  4. SORTBY(A2:A9,B2:B9,-1){8 rows × 1 col} "Marcus", "Lars", "Tom", "Hana", …

    SORTBY: Sorts a range by other ranges. Spills.

    • arrayA2:A98 rows × 1 col: "Priya", "Tom", "Ines", "Marcus", …
    • by_array1B2:B98 rows × 1 col: $48,200, $61,500, $39,800, $72,300, …
    • sort_order1-1-1

In Google Sheets

Google Sheets has no SORTBY. Use =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.

FormulaResultWhen to use it
=INDEX(A2:A9,MATCH(LARGE(B2:B9,SEQUENCE(8)),B2:B9,0))spills 8 × 1Different resultWorks 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

In 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.