You can not define a custom sort order in Google Sheets Query using the Order By Clause. But there is a workaround. Custom sort order in Google Sheets Query is possible using the Match function in Query data.

New to the powerful Query function in Sheets? Please check my function guide then.

Let’s see how to define a custom sort order in Query with a workaround.

The Order By Clause in Query helps to sort the rows by values in any column that’s specified via column identifier.

The column identifier can also be the output of scalar/aggregation functions or operators. But that doesn’t include the custom sort order option.

The solution is to use a virtual column in the Query ‘data’ that is generated using the Match function and use that column in the Order By clause.

QUERY(data, query, [headers])

Formula Example to Custom Sort Order in Google Sheets Query

The first step is to generate a virtual column to use in the Query Order By Clause. That means other than Columns A and B in the source data, there will be a virtual third column.

In the above example, I have sorted the data in the range A2:B based on column B in the custom sort order in D2:E. That custom sort order is;

={"Finished";"Started";"Not Yet Started"}

Just enter this formula in any blank cell you can see the result as below.

I want the Query to sort the data by this order in column B. What you want to do is match the values in Column B with these three values.

MATCH(search_key, range, [search_type])

That means the values in Column B are the search keys, and the above three values are the range.

See that formula that generates the virtual sort column.

=ArrayFormula(match(B2:B,{"Finished";"Started";"Not Yet Started"},0))

This formula you can use in Query as the third column and use in the Order By Clause.

Before that see what the above formula returns. This is just for your info. No need to enter this formula in C2. We can directly use this in Query.

Here is the custom sort Query formula.

=ArrayFormula(query({A2:B,match(B2:B,{"Finished";"Started";"Not Yet Started"},0)},"Select Col1, Col2 where Col1 is not null order by Col3"))

I have moved the ArrayFormula to the outer and used the Match formula as the virtual column. Since it’s the third column in the range (virtual column not physical), I have used the column identifier “Col3” to sort.

This is the workaround to get a custom sort order in Google Sheets Query.

Additional Resources:

1. Sort by Custom Order in Google Sheets [How to Guide].
2. The Formula to Sort By Month Name in Google Sheets.
3. Sort Data in Google Sheets – Different Functions and Sort Types.