SORTBY

In this comprehensive guide, we will explore the SORTBY function in Excel, which is a powerful formula that allows you to sort a range or an array based on the values in a corresponding range or array. This function is particularly useful when you need to sort data by multiple criteria or when you want to sort data that is not in the same range as the values you want to sort by. We will cover the syntax, examples, tips and tricks, common mistakes, troubleshooting, and related formulae for the SORTBY function.

SORTBY Syntax

The syntax for the SORTBY function in Excel is as follows:

=SORTBY(array, by_array1, [sort_order1], [by_array2], [sort_order2], …)

Here is a breakdown of the arguments in the SORTBY function:

• array: This is the range or array that you want to sort.
• by_array1: This is the first range or array that you want to sort the data by. The length of this array must be the same as the length of the ‘array’ argument.
• sort_order1 (optional): This is a number that specifies the sort order for the first ‘by_array’. Use 1 for ascending order (default) and -1 for descending order.
• by_array2, sort_order2, … (optional): You can specify additional ranges or arrays to sort by, along with their corresponding sort orders. This is useful when you want to sort the data by multiple criteria.

SORTBY Examples

Let’s look at some examples of how to use the SORTBY function in Excel:

Example 1: Basic SORTBY usage

Suppose you have a list of products with their prices and quantities in stock. You want to sort the products by their prices in ascending order. You can use the SORTBY function as follows:

=SORTBY(A2:A10, B2:B10, 1)

This formula will sort the products in the range A2:A10 based on their prices in the range B2:B10 in ascending order.

Example 2: SORTBY with multiple criteria

Continuing with the previous example, suppose you want to sort the products first by their prices in ascending order, and then by their quantities in stock in descending order. You can use the SORTBY function with multiple criteria as follows:

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

This formula will sort the products in the range A2:A10 based on their prices in the range B2:B10 in ascending order, and then by their quantities in the range C2:C10 in descending order.

SORTBY Tips & Tricks

Here are some tips and tricks to help you get the most out of the SORTBY function in Excel:

1. Remember that the SORTBY function is only available in Excel 365 and Excel 2021, as it is part of the new dynamic array functions. If you are using an older version of Excel, you will need to use alternative methods, such as the SORT function combined with INDEX and MATCH.
2. When using the SORTBY function with multiple criteria, keep in mind that the order in which you specify the criteria matters. The function will first sort the data based on the first ‘by_array’, and then by the second ‘by_array’, and so on.
3. If you want to sort the data in the original range, you can use the SORTBY function in combination with the INDEX function. For example:

   =INDEX(A2:A10, SORTBY(SEQUENCE(ROWS(A2:A10)), B2:B10, 1))

4. You can also use the SORTBY function to sort data based on a custom sort order. For example, you can create a lookup table with the custom sort order and use the XLOOKUP function to retrieve the corresponding sort order values for the ‘by_array’.

Common Mistakes When Using SORTBY

Here are some common mistakes that users make when using the SORTBY function in Excel:

1. Using the SORTBY function in older versions of Excel that do not support dynamic array functions. As mentioned earlier, the SORTBY function is only available in Excel 365 and Excel 2021.
2. Not specifying the correct sort order (1 for ascending and -1 for descending) for the ‘by_array’ arguments. By default, the SORTBY function will sort the data in ascending order if the sort order is not specified.
3. Using ranges or arrays with different lengths for the ‘array’ and ‘by_array’ arguments. The length of the ‘by_array’ must be the same as the length of the ‘array’ argument.

Why Isn’t My SORTBY Working?

If your SORTBY function is not working as expected, here are some possible reasons and solutions:

1. Make sure you are using Excel 365 or Excel 2021, as the SORTBY function is not available in older versions of Excel.
2. Check the syntax of your SORTBY function and ensure that you have specified the correct arguments, including the sort order for each ‘by_array’.
3. Ensure that the ‘array’ and ‘by_array’ arguments have the same length. If they have different lengths, the SORTBY function will return an error.
4. If you are using the SORTBY function with multiple criteria, make sure that the order of the criteria is correct. The function will first sort the data based on the first ‘by_array’, and then by the second ‘by_array’, and so on.

SORTBY: Related Formulae

Here are some related formulae that you might find useful when working with the SORTBY function in Excel:

1. SORT: The SORT function is another dynamic array function that allows you to sort a range or an array based on one or more columns. Unlike the SORTBY function, the SORT function sorts the data based on the values in the same range or array.
2. FILTER: The FILTER function allows you to filter a range or an array based on one or more criteria. You can use the FILTER function in combination with the SORTBY function to first filter the data and then sort it based on the desired criteria.
3. INDEX: The INDEX function returns the value at a specific row and column in a range or an array. You can use the INDEX function with the SORTBY function to sort the data in the original range, as shown in the tips and tricks section.
4. MATCH: The MATCH function searches for a specific value in a range or an array and returns the relative position of the value. You can use the MATCH function with the SORTBY function to sort the data based on a custom sort order.
5. XLOOKUP: The XLOOKUP function is a versatile lookup function that allows you to search for a value in a range or an array and return the corresponding value from another range or array. You can use the XLOOKUP function with the SORTBY function to sort the data based on a custom sort order, as mentioned in the tips and tricks section.