Summary

The Excel SORTBY function is used to sort the contents of a range or array based on the values from another range or array. This function is highly versatile for sorting data based on external criteria, making it ideal for organizing and analyzing datasets where the sorting criteria are not part of the primary data.

Syntax

				
					=SORTBY(array, by_array, [sort_order], [array/order], ...)

array: The range or array to sort.
by_array: The range or array containing the values to sort by.
sort_order: [Optional] Sort order. Use 1 for ascending (default), or -1 for descending.
array/order: [Optional] Additional array and sort order pairs.

Return value

A sorted array based on the specified criteria.

How to use

Use SORTBY by specifying the primary array you want to sort and the array that contains the sorting criteria (by_array). Optionally, define the order of the sort (ascending or descending) and add additional sorting levels if needed.

Examples

Simple SORTBY

Sorting Based on an External Criteria: To sort a list of names based on corresponding scores:

				
					=SORTBY(B2:B10, C2:C10, -1)

This formula sorts the names in B2:B10 in descending order based on their corresponding scores in C2:C10.

SORTBY with Multiple Sort Levels

Multi-Level Sorting of Data: Sorting by two criteria, first by status and then by date:

				
					=SORTBY(A2:C10, D2:D10, 1, E2:E10, -1)

Here, the data in A2:C10 is first sorted by status in D2:D10 in ascending order and then by date in E2:E10 in descending order.

SORTBY for Custom Order

Custom Sorting with Array Constants: Sorting data using a custom order defined by an array constant:

				
					=SORTBY(A2:A6, {2, 1, 4, 3, 5})

This sorts the range A2:A6 based on a custom sort order defined by the array constant {2, 1, 4, 3, 5}.

SORTBY for Horizontal Sorting

Horizontal Sorting Based on Row Values: To sort data horizontally based on values in a specific row:

				
					=SORTBY(A1:E1, A2:E2, 1)

This formula sorts the data in A1:E1 horizontally based on the values in A2:E2.

SORTBY with Multiple Criteria

Complex Sorting Using Multiple Criteria and Different Orders: Sorting a dataset first by category and then by sales in opposite orders:

				
					=SORTBY(A2:B10, C2:C10, -1, D2:D10, 1)

This sorts the range A2:B10 first by category in C2:C10 in descending order, followed by sales in D2:D10 in ascending order.

Additional Notes

SORTBY can be used with both vertical and horizontal arrays.
The dimensions of the by_array must be compatible with the array being sorted, or SORTBY will return a #VALUE! error.
The function is dynamic; changes in the source data or criteria will automatically update the sorted results.
SORTBY is especially useful when sorting needs to be based on data that is not part of the array being sorted.