Dynamic arrays

Sort by multiple columns with a formula (SORTBY)

SORTBY sorts a range by one or more other ranges, each with its own direction, and the result updates automatically.

Excel 365 / Excel 2021+

The formula

ExcelExcel
=SORTBY(A2:C8,B2:B8,1,C2:C8,-1)
In E2, spills: [Ana, East, 420], [Fay, East, 130], [Cara, East, 90], [Dev, North, 310], [Gus, North, 60], [Eli, West, 275], [Ben, West, 150]
Excel (SORT by column index)Excel
=SORT(A2:C8,{2,3},{1,-1})
In I2, spills: [Ana, East, 420], [Fay, East, 130], [Cara, East, 90], [Dev, North, 310], [Gus, North, 60], [Eli, West, 275], [Ben, West, 150]
Google SheetsGoogle Sheets
=SORT(A2:C8,2,TRUE,3,FALSE)
In M2, spills: [Ana, East, 420], [Fay, East, 130], [Cara, East, 90], [Dev, North, 310], [Gus, North, 60], [Eli, West, 275], [Ben, West, 150]
✓ Tested. All 2 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown. 1 result for formulas tagged Google Sheets was checked with a reference implementation of the documented behavior, because LibreOffice can't run that function.

Worked example

Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.

ABCDEFGHIJKLMNO
1RepRegionAmount
2AnaEast420AnaEast420AnaEast420AnaEast420
3BenWest150FayEast130FayEast130FayEast130
4CaraEast90CaraEast90CaraEast90CaraEast90
5DevNorth310DevNorth310DevNorth310DevNorth310
6EliWest275GusNorth60GusNorth60GusNorth60
7FayEast130EliWest275EliWest275EliWest275
8GusNorth60BenWest150BenWest150BenWest150

How it works

A2:C8
The data to return, sorted.
B2:B8,1
First sort key: Region, ascending (1).
C2:C8,-1
Second key: Amount, descending (-1). Add more pairs as needed.
{2,3},{1,-1}
SORT alternative: sort by the 2nd then 3rd column of the range.

Common errors and fixes

Why does the formula return #VALUE!?

A sort range has a different height than the data.

Why does the formula return #SPILL!?

Output area isn't empty.

Sheets #ERROR!?

Sheets has no SORTBY. Use SORT(range, column, TRUE/FALSE, …).

Does this work in Google Sheets?

Yes. Use the formulas tagged "Google Sheets" on this page; formulas tagged only "Excel" use Excel-specific syntax.