SORTBY Function

Sorts one range by the values in another.

Category

Lookup & Reference

Available in

Microsoft 365, Excel 2021+

Returns

A spilled, sorted array

Taught hands-on in Level 4: Advanced Dynamic Arrays & Logic · Dynamic Arrays module

Overview

SORTBY sorts your data using a separate range as the key — that key doesn't even need to appear in the result.

It's easier than SORT for multi-level sorts because each key and direction is listed as a pair.

Syntax

=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], …)

Arguments

ArgumentRequired?What it does
arrayYesThe data to return, sorted.
by_array1YesThe range to sort by. Must be the same height as array.
sort_order1Optional1 = ascending (default), -1 = descending.
by_array2, sort_order2, …OptionalFurther sort keys.

Examples

Example 1 — Names sorted by a hidden score

Return just the names, ordered by sales (highest first).

D2fx=SORTBY(A2:A4, B2:B4, -1)
ABCD
1RepSalesRanked
2Ana400Cara
3Ben350Ana
4Cara500Ben
=SORTBY(A2:A4, B2:B4, -1) Explain

Example 2 — Random shuffle

Sort by a column of random numbers to shuffle a list.

=SORTBY(A2:A20, RANDARRAY(ROWS(A2:A20))) Explain
Two-level sort: =SORTBY(A2:C100, B2:B100, 1, C2:C100, -1) — region ascending, then sales descending.

Common mistakes

  • #VALUE! errors. by_array isn't the same height (or width) as array.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.