Excel SORTBY function is a lookup and reference formula that sorts the values in a range or array based on the values in a corresponding range or array. In this guide, we’re going to show you how to use the Excel SORTBY function and also go over some tips and error handling methods.
- At the time of writing this article, Microsoft announced that this formula is currently only available to a number of select insider users. When it's ready, the feature is planned for release for Office 365 users.
The range or array to be sorted.
The first array or range to sort by.
Optional. A numeric value indicating the order of sorting for by_array1.
The nth array or range to sort by.
Optional. A numeric value indicating the order of sorting for by_arrayN.
Note that we're using named ranges in our sample formulas to make them easier to read. This is not required.
Sort by multiple columns
The COUNTA function in the formula retrieves the size of the array dynamically.
- If you need to sort a range or array based on a different range or array, you should use SORTBY. The SORTBY function can handle column changes because it references a range rather than an index number like the SORT function does.
- If the list to be sorted is based on its own values, the SORT function is more suitable.
If there isn't enough space for adding the results below the formula, you will see a #SPILL! error. Excel marks the target range with dashed lines. Clear the contents of the cells in this range, and Excel will automatically update the results.
- If array and by_array arguments have different sizes, the function returns a #VALUE! error.
- If [sort_order] argument is neither 1 nor -1, the function returns a #VALUE! error.