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
={"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:
How did you avoid the following Error Message by not including Col3 in the “Select” portion of your query?
"Error COL_IN_ORDER_MUST_BE_IN_SELECT: 'Col3'"
Hi, Dave,
I don’t get that error on my side. Can you share a sample?
Awesome, That’s brilliant.
Just for completeness, why does the ArrayFormula need to be moved to the outside, and what happens if it isn’t placed there?
Thank again – This should allow ordering output by weekdays in proper order!
Hi, Stu,
It won’t make any difference in this case. But make it a practice.
If you have two or more ArrayFormulas in a combination formula, you can remove all that by placing only one ArrayFormula in the front.