SORTBY

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

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

arrayThe array or range to sort.
by_array1The 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

 ABC
1=SORTBY(B1:C4, B1:B4) = { "one", 30, "one", 50, "three", 10, "two", 20 }one30
2=SORTBY(B1:C4, B1:B4, 1) = { "one", 30, "one", 50, "three", 10, "two", 20 }two20
3=SORTBY(B1:C4, B1:B4, -1) = { "two", 20, "three", 10, "one", 30, "one", 50 }one50
4=SORTBY(B1:C4, C1:C4, 1) = { "three", 10, "two", 20, "one", 30, "one", 50 }three10
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 returned
8 - If any of the cells in the spill range are not blank, then #SPILL! is returned.

© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited Top