How to sort a Google Sheets Query by a custom ordering

Question

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.

Answer 1

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.

Answer 2

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.

There is a worked example showing this here: https://docs.google.com/spreadsheets/d/1s8MngBnoJ53czPOLlBJ3DRAbZHc4JZXd0KPeRBM4_tQ/edit?usp=sharing

Answer 3

Actually you don't need arrayformula() because sort() function returns rows and columns already.

In the image up there, in D5, (not E5, the blue rectangle should be at D5 in the image) you can just type:

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

That's it.

Special thanks to Jeeped. I found this while I was playing with your answer, using match() in sort() function, which I didn't know before.