Hello all. In this section, let’s look at the “sortby” function in excel. Unlike the sort function, sortby sorts the values in a range or array based on value in another list or array. Please note the range or array, that is used to sort, doesn’t necessarily need to appear in the output.
=sortby(array, by_array, [sort_order], [array/order]..)
Here array is the value that needs to be sorted. by_array is the array to sort by. [sort_order] defines whether descending or ascending. [array/order] is the additional array and sort_order pairs.
We will be using the below data set and try “sortby” without providing any optional arguments. We are sorting by the number of matches
By default, the function sorts the values in ascending order. Let’s sort by descending order and output only the name
The sort by array can include various other functions like match, len etc. Let’s look at an example. Let’s sort Name by length of the name
In the above example, we check the length of the text in A2:A11 and sort them in ascending order.
Let’s look at sortby with match function
In the above example, match function looks up the role against the list in M2:M4 and returns either 1 or 2 or 3. Output of the match function will 10 rows with a combination of 1/2/3. This will be sort by array and outputs the array based on the role. Note: If you want the role “allrounder” to be listed 1st in the output, replace Batsman with allrounder in M2.
Points to note
- Both “array” and “by_array” should have the same number of rows
- “by_array” can be only one row or one column
I hope you like this section. Please help us with your valuable feedback. Thanks for reading!