SORTBY

Formulas / SORTBY
Sort a range or array by column.
=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2],…)
  • array - required, the array or range to sort
  • by_array1 - required, the array or range to sort on
  • sort_order1 - [OPTIONAL] order to use for sorting
  • by_array2 - [OPTIONAL] the array or range to sort on
  • sort_order2 - [OPTIONAL] order to use for sorting

Examples

  • =SORTBY(A1:A5,B1:B5,1)

    For example, this sorts the values in A1:A5 according to the values in B1:B5 in ascending order.

  • =SORTBY(A1:A5,{3;5;2;1;4})

    The SORTBY function can also be used with an array constant. An array constant is an array of values specified directly in the formula. Array constants are enclosed in curly braces {} with each array element separated by a semicolon ;.For example, the function sorts the values in A1:A5 using the array constant 3, 5, 2, 1, and 4.

  • =SORTBY(A1:A5,B1:B5,C1:C5,1)

    The SORTBY function can also be used to sort data based on multiple criteria. This can be done by providing multiple ranges in the range2 argument of the function. For example, this formula sorts the values in A1:A5 according to the values in B1:B5 and C1:C5 in ascending order.

Summary

The SORTBY function allows users to sort a range or array according to the values in a corresponding range or array. It returns an array and requires by_array arguments that must be one row or one column wide and of the same size as the sort_order arguments. The array must be sorted in ascending order.

  • The SORTBY function sorts a range or array based on the values of another range or array, which does not need to appear in the results of the function call.
  • The by_array argument automatically determines the sort orientation and can be a vertical range or a horizontal range.
  • The sort_order argument determines the sort direction, either 1 for ascending or -1 for descending.


Frequently Asked Questions

What is the SORTBY function?
The SORTBY function is a way to sort a range or array based on the values in another array. It returns an array with the sorted values.
How does the SORTBY function work?
The SORTBY function sorts a range or array based on the values in corresponding cells. For example, you could sort a table by region in ascending order then by each person's age in descending order using RANDARRAY and COUNTA.
What are some examples of using the SORTBY function?
  • You can sort a range of numbers from smallest to largest.
  • You can sort a range of text alphabetically.
  • You can sort a range of dates from oldest to newest.

Related Functions

Make Better Decisions
With Data

Analyze data, automate reports and create live dashboards
for all your business applications, without code. Get unlimited access free for 14 days.