One challenge that comes up frequently when sorting is a need to sort in a custom order. For example, in this worksheet, we have a list of opportunities, and we want to sort the list in the order that stages appear in column H. How can we do this with a formula? Well, to start off, if we try to sort the data with the SORTBY function, and provide the list of stages for the sort_by array, I’ll get an error. This is because the source data has 12 rows, and the range that contains the stages only has 4 rows. The SORTBY requires the array and sort_by arguments to have compatible dimensions. So, to work out a solution, let’s start by adding a helper column. Our goal here is to create an array with values that represent the required sort order. How can we do that? Well, the MATCH function is actually a good solution here. What we want is a position for each stage. I can get that by feeding in the values from column E as lookup values into MATCH, then matching against the values in column H. Note this configuration is “backwards” from a normal MATCH formula set-up. Also note that we need to set this as an exact match. When I enter the MATCH formula, we get a result for each row of the data. Notice all Leads are 1, all Quotes are 2, and so on. In other words, these values represent the order that stages appear in column H. Now that we have a helper column in place, I can enter a SORTBY formula, and use these values to sort by. When I press enter, all data is sorted as needed. Now let’s remove the helper column. First, I’ll copy the MATCH formula to the clipboard. Then I’ll edit the SORTBY formula and replace the sort_by argument with the formula I copied. If I use the F9 key, I can see that the array that MATCH returns contains the same values that we have in the helper column. And, when I enter the formula, the data is still sorted correctly. Now I can remove the helper column, and everything still works fine.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
