# SORTBY

SORTBY(array, by_array1 [,sort_order1] [,by_array2] [,sort_order2] [..])

Returns the array of data that has been sorted based on multiple columns.

 array The array or range to sort. by_array1 The array or range to sort on. sort_order1 (Optional) The order to use for sorting:1 = Ascending (default)-1 = Descending by_array2 (Optional) The array or range to sort on. sort_order2 (Optional) The order to use for sorting:1 = Ascending (default)-1 = Descending.

 REMARKS
 * This function was added in Excel 2021.* This function can create a Dynamic Array Formula.* This function can be used to return a table sorted by more than one column.* If "array" and "by_array" are different sizes, then #VALUE! is returned.* The "by_array" must be either a single column range or a single row range.* If "sort_order" is not 1 or -1, then #VALUE! is returned.* If there are not enough blank cells to display the full array, then #SPILL! is returned.* You can use the FILTER function to filter and sort your data.* You can use the SORT function to return an array of data that has been sorted.* For the Microsoft documentation refer to support.microsoft.com

 A B C 1 =SORTBY(B1:C4, B1:B4) = { "one", 30, "one", 50, "three", 10, "two", 20 } one 30 2 =SORTBY(B1:C4, B1:B4, 1) = { "one", 30, "one", 50, "three", 10, "two", 20 } two 20 3 =SORTBY(B1:C4, B1:B4, -1) = { "two", 20, "three", 10, "one", 30, "one", 50 } one 50 4 =SORTBY(B1:C4, C1:C4, 1) = { "three", 10, "two", 20, "one", 30, "one", 50 } three 10 5 =SORTBY(B1:C4, C1:C4, -1) = { "one", 50, "one", 30, "two", 20, "three", 10 } 6 =SORTBY(B1:C4, B1:B4, 1, C1:C4, -1) = { "one", 50, "one", 30, "three", 10, "two", 20 } 7 =SORTBY(FILTER(B1:C4, C1:C4<25), C1:C4, 1) = { "three", 10, "two", 20 } 8 =SORTBY(B1:C4, A1) = #VALUE! 9 =SORTBY(B1:C4, B1:B4) = #SPILL!

 1 - Sort all the rows in the range "B1:C4" by column 1, in ascending order.2 - Sort all the rows in the range "B1:C4" by column 1, in ascending order.3 - Sort all the rows in the range "B1:C4" by column 1, in descending order.4 - Sort all the rows in the range "B1:C4" by column 2, in ascending order.5 - Sort all the rows in the range "B1:C4" by column 2, in descending order.6 - Sort all the rows in the range "B1:C4" by column 1, ascending AND by column 2, descending.7 - If "array" and "by_array" are different sizes, then #VALUE! is returned8 - If any of the cells in the spill range are not blank, then #SPILL! is returned.

© 2024 Better Solutions Limited