# How to sort a Google Sheets Query by a custom ordering

I have a table of choir members with a “name” and a “voice type” column. How can I write a query that orders them by decreasing pitch level of the voice type (i.e. soprano > contralto > tenor > bass)?

Is there in the Google Query Language something similar to FIELD() in MySQL?

I also tried the SORT function, but it does not seem to have an option for custom ordering, either.

• I wish there was a way to set up such a sort so that when I click a column and go to Data -> Sort Sheet By Column ... it would sort using my custom ordering! – Michael Sep 26 '18 at 16:14

You can add MATCH to your SORT for a custom order list.

The formula in D5 is,

``````=ArrayFormula(sort(A\$2:A\$99,match(left(\$B\$2:\$B\$99,3),{"sop";"con";"ten";"bas"},0),1))
``````

Copy to E5. Without speed testing the calculation cycles it's debatable whether using the Left function to peel off the first three characters is seriously detrimental but it certainly makes creating the constant array for the Match range lookup easier to create and maintain. Be careful that you do not duplicate the first three characters if you expand the list.

• I'm having trouble understanding this part in your formula: `=match(left(\$B\$2:\$B\$99,3),{"sop";"con";"ten";"bas"},0)`.. i ran this on it's own and I got this list :{1,4,2,3,4,2,3,1}.. but according to the sort docs the `sort_column` is: The index of the column in range or a range outside of range containing the values by which to sort.. I just can't reconcile the two.. – abbood Jul 8 '17 at 8:13

Make a new sheet inside your current spreadsheet. Put two columns in it:

``````VoiceType   Sort order
SOPRANO     1
CONTRALTO   2
TENOR       3
BASS        4
``````

In your main worksheet (the one with the list of people), in a new column put a formula like this:

``````=vlookup(upper(B2),Sheet2!A\$2:B\$5,2,0)
``````

Note that I have used the UPPER() function in case some of the voice types are entered as "tenor" or "Tenor.

``````=sort(A2:B99,match(B2:B99,{"SOPRANO","CONTRALTO","TENOR","BASS"},0),1)