Sort by a Helper or Custom Order with SORTBY

365 / 2021SORTBY

SORTBY sorts one range by the values in another — even a computed helper or a custom priority order — without adding columns to your data.

Quick formula: sort names by a separate score column, descending:

=SORTBY(Names, Scores, -1)

The first argument is what to return; the rest are sort-key/order pairs. The keys can be any range, not just a column inside the data.

Functions used (tap for the full reference guide):

SORTBY MATCH

The example

Names sorted by score without touching the data layout.

	A	B
1	Name	Score
2	Ann	95
3	Cy	88

The formula

Sort one array by another:

=SORTBY(Names, Scores, -1) // -1 = descending

How it works

SORTBY separates “what to return” from “how to order”:

First argument: the array to return.
Then key/order pairs: a sort-key range and 1 (ascending) or -1 (descending).
The key can be a computed array — e.g. sort by LEN(names) — without a helper column on the sheet.
Add more key/order pairs for multi-level sorts: by region, then by sales descending.

Custom (non-alphabetical) order: sort by a MATCH against a priority list — =SORTBY(data, MATCH(status, {"High";"Med";"Low"}, 0)) orders rows High→Med→Low instead of alphabetically. That’s the trick SORT alone can’t do.

Try it: interactive demo

Live demo

Names + scores; sorted by score.

Order

Variations

Sort by computed key

By name length:

=SORTBY(Names, LEN(Names))

Custom priority order

High → Med → Low:

=SORTBY(data, MATCH(status, {"High";"Med";"Low"}, 0))

Multi-level

Region asc, sales desc:

=SORTBY(data, Region, 1, Sales, -1)

Pitfalls & errors

Key length must match. Each sort-key range must have the same number of rows as the return array, or #VALUE!.

SORTBY vs SORT. SORT orders by a column within the data by position; SORTBY orders by a separate key — better for helper/computed/custom orders.

365/2021 only. Older Excel needs a helper column plus a manual sort.

Practice workbook

📊

Download the free Sort by a Helper or Custom Order with SORTBY practice workbook

SORTBY examples (formula text + result) with computed-key, custom-priority, and multi-level variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I sort by a separate column in Excel?

Use =SORTBY(returnArray, keyArray, order). The key can be any range or computed array, not just a column inside the data. Requires Excel 365/2021.

How do I sort in a custom (non-alphabetical) order?

Sort by a MATCH against a priority list: =SORTBY(data, MATCH(status, {"High";"Med";"Low"}, 0)) orders High→Med→Low.

What's the difference between SORT and SORTBY?

SORT orders by a column position within the data; SORTBY orders by a separate key array, which is better for helper columns, computed keys, or custom orders.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Sort by multiple columns · Unique sorted list · Rank values

Function references: SORTBY · MATCH