SORTBY
Sorts a range based on the values in another range.
=SORTBY(array, by_array1, [sort_order1], ...)This static page keeps the function indexed for search, while the spreadsheet app handles interactive exploration and saved formulas.
What the function does
Overview
Sorts a range based on the values in another range.
The SORTBY function is a dynamic array formula used to sort a range or array based on the values in one or more corresponding arrays. Unlike the standard SORT function, which requires the sort index to be part of the selected range, SORTBY allows you to sort by data that is not included in the final output, providing significantly more flexibility for reporting and data visualization. It supports multiple levels of sorting (e.g., sort by Category, then by Date) by adding pairs of rray and rder arguments. A critical technical requirement is that every 'rray' must have dimensions compatible with the source 'array' argument (e.g., same number of rows for vertical data). It is best practice to use SORTBY when the sort key is a calculated value or a helper column residing outside the target display range. Note that the function is dynamic, meaning it will automatically update when source values change, but it will return a #SPILL! error if the destination range is obstructed.
Quick reference
Syntax
=SORTBY(array, by_array1, [sort_order1], ...)Inputs
Arguments
Example: A2:B100
Example: C2:C100
Example: -1
Formula patterns
Examples
Sort names by score descending
=SORTBY(A2:A10, B2:B10, -1)Multi-level sort: Category ascending then Price descending
=SORTBY(A2:C20, B2:B20, 1, C2:C20, -1)Sort list by character length of text
=SORTBY(A2:A15, LEN(A2:A15), 1)Avoid these issues
Common Errors
#VALUE!
Cause: The dimensions of the 'by_array' do not match the dimensions of the source 'array'.
Fix: Ensure both ranges cover the exact same number of rows (for vertical lists) or columns (for horizontal lists).
#SPILL!
Cause: The output area for the sorted data is blocked by existing text, values, or merged cells.
Fix: Delete any data in the cells where the formula is trying to 'spill' its results or move the formula to an empty area.
Platform support
Compatibility
Dynamic array function. Excel 2021+ and Microsoft 365
Source: Microsoft Support
Common questions
Frequently Asked Questions
Sorts a range based on the values in another range.
array: The range to sort rray1: The range to sort by rder1: 1 = Ascending, -1 = Descending
#VALUE!: Ensure both ranges cover the exact same number of rows (for vertical lists) or columns (for horizontal lists). #SPILL!: Delete any data in the cells where the formula is trying to 'spill' its results or move the formula to an empty area.