Quick Navigation
SORTBY Function
Summary
The SORTBY function dynamically sorts a range or array based on criteria from one or more corresponding arrays. This powerful dynamic array function enables flexible sorting operations beyond simple alphabetical or numerical order.
Syntax
=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], …)Parameters
| Parameter | Type | Required | Description |
|---|---|---|---|
| array | Array/Range |
Yes | Range or array containing values to sort |
| by_array1 | Array/Range |
Yes | Criteria range determining sort order |
| sort_order1 | Number |
No | Optional: 1=ascending, -1=descending (defaults to 1) |
| by_array2 | Array/Range |
No | Optional: Secondary sort criteria |
| sort_order2 | Number |
No | Optional: Sort order for by_array2 |
Using the SORTBY Function
SORTBY revolutionizes data organization by allowing custom sort criteria while preserving row relationships. Unlike traditional sort operations, SORTBY creates dynamic arrays that automatically update and spill into adjacent cells.
Common SORTBY Examples
Sort Names by Age (Ascending)
=SORTBY(A2:B10, B2:B10)Sorts names and ages by age column in ascending order (youngest first)
Multi-Level Sort
=SORTBY(A2:C10, B2:B10, 1, C2:C10, -1)Primary sort by Region (ascending), secondary sort by Sales (descending)
Randomized Sort with Dynamic Arrays
=SORTBY(A2:A11, RANDARRAY(ROWS(A2:A11)))Creates randomized list that refreshes on recalculation
Temperature Sort
=SORTBY(B2:C10, B2:B10)Sorts temperature and rainfall data by temperature column
Frequently Asked Questions
Common Errors and Solutions
#VALUE!
Cause: Sort order is not 1 or -1
Solution: Use only 1 (ascending) or -1 (descending) for sort_order
#SPILL!
Cause: Blocked spill range
Solution: Clear cells where results should appear
#REF!
Cause: Source workbook closed (cross-workbook)
Solution: Keep both workbooks open
Notes
- by_array must be single row or single column
- All arrays must have identical dimensions
- Works with spilled ranges using # operator
- Limited cross-workbook functionality requires both files open
Compatibility
Available in: Excel 365, Excel 2021
Not available in: Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007
Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 365, Excel 2021+