The `SORTBY` function in Excel is used to sort the contents of a range or array based on the values in one or more corresponding arrays. This function is particularly useful when you want to sort data by multiple criteria or when you want a dynamic sort that updates automatically if the underlying data changes.
Here’s how to use the `SORTBY` function:
Syntax
SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2], …)
- `array`: This is the range or array that you want to sort.
- `by_array1`: The array or range whose values you want to sort `array` by.
- `sort_order1`: Optional. The order you want to sort the `by_array1`. Use `1` for ascending (default) or `-1` for descending.
- `by_array2`, `sort_order2`, …: Additional sorting criteria.
Example
Suppose you have the following data in cells A1:B4:
| Product | Price |
|———|——-|
| Apple | 2 |
| Banana | 1 |
| Cherry | 3 |
| Date | 2 |
If you want to sort this data by the price in ascending order, you would use the `SORTBY` function like this:
=SORTBY(A2:B5, B2:B5, 1)
Steps:
- Select the cell where you want the sorted data to appear.
- Type `=SORTBY(` followed by your desired arguments.
- Enter the `array` (here, the data range A2:B5).
- Specify the `by_array1` (here, the range B2:B5 for the prices you are sorting by).
- Define the `sort_order1`. If omitted, it defaults to 1 (ascending).
Sorting by Multiple Criteria
If you wanted to sort first by price (ascending) and then by product name (descending), you could use:
=SORTBY(A2:B5, B2:B5, 1, A2:A5, -1)
Points to Remember
- `SORTBY` is available in Excel 365, Excel 2019, and later versions.
- It is a dynamic array function, meaning it can automatically spill the output into adjacent cells as needed.
- Changes in the source data update the sorted list automatically, preserving the sort order specified.
Using `SORTBY`, you can create more flexible and advanced sorts compared to simple sorting options in Excel.
Unlock Your Potential
Excel
Access
Power BI
