Sort by custom list with SORTBY

One challenge that comes up frequently when sorting is a need to sort in a custom order.

The SORTBY requires thearrayandsort_byarguments 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, theMATCH functionis actually a good solution here.

What we want is a position for each stage.

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.

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 thesort_byargument with the formula I copied.

And, when I enter the formula, the data is still sorted correctly.

Now I can remove the helper column, and everything still works fine.