SORTBY
The SORTBY function is used to sort a range or array based on the values in another range or array. It is handy for reordering data based on specific criteria.
Syntax
=SORTBY(array, by_array, [sort_order], [sort]) Arguments
| Argument | Required | Description |
|---|---|---|
| array | Yes | The range or array to be sorted. |
| by_array | Yes | The range or array containing the values to sort by. |
| sort_order | No | The order in which to sort. 1 for ascending, -1 for descending. Defaults to 1 if omitted. |
| sort | No | The sort order for text values. 0 for case-sensitive, 1 for not case-sensitive. Defaults to 0 if omitted. |
About
When you find yourself in need of rearranging data in Excel based on specific criteria, the SORTBY function comes to the rescue. It offers a simple yet powerful solution for sorting a range or array with respect to the values in another range or array, enabling you to customize the order of your data seamlessly. This function is particularly useful in scenarios where you need to organize data based on related information or perform custom sorting operations with ease.
Examples
Suppose you have a list of student names in column A and their corresponding scores in column B. You want to sort the names alphabetically based on the scores in descending order. The SORTBY formula would be: =SORTBY(A2:B10, B2:B10, -1)
Imagine you have a column of sales data in column A and a corresponding column of product names in column B. You need to sort the sales figures in ascending order while keeping the product names aligned accordingly. The SORTBY formula would be: =SORTBY(A2:B20, A2:A20)
Imagine you have a column of sales data in column A and a corresponding column of product names in column B. You need to sort the sales figures in ascending order while keeping the product names aligned accordingly. The SORTBY formula would be: =SORTBY(A2:B20, A2:A20)
Tips & notes
The SORTBY function requires the arrays or ranges provided as arguments to be of the same length. It is essential to ensure that the data in these arrays align properly to avoid unexpected results. Additionally, you can customize the sorting behavior using the optional arguments to cater to specific requirements.
Common questions
Can I sort data based on multiple criteria using the SORTBY function?
No, the SORTBY function sorts data based on a single range or array. If you need to apply multiple sorting criteria, you may consider using other functions like SORT or creating helper columns to combine sorting conditions.
Is the sort order case-sensitive by default in the SORTBY function?
Yes, by default, the sort order for text values in the SORTBY function is case-sensitive. You have the option to change this behavior by specifying 1 for the sort argument to make the sorting not case-sensitive.
Can the SORTBY function handle empty cells or errors in the data ranges?
Yes, the SORTBY function can handle empty cells or errors in the data ranges. It will sort the non-error values while keeping any empty cells or errors in their original positions relative to the sorted data.