Custom Sort Order in Google Sheets Query [Workaround]

Published on

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])
Custom Sort Order in Google Sheets Query

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.

define custom sort values for Query

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.

The role of MATCH in query custom sort

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.
Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

4 COMMENTS

  1. 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'"

  2. 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.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.