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.
How to sort a Google Sheets Query by a custom ordering
google spreadsheets;google spreadsheets query
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.