By default, you aren’t allowed to use the sort_column name instead of
Here related functions mean the functions in Google Sheets that offers to sort data. They are SORT, SORTN, and QUERY.
Sort_Column name means the column name to sort. By default, you can only use the column index number in both SORT and SORTN functions.
Regarding Query there you can use column header label or column index number. See the picture and the details
In the above screenshot, the number 1, 2 …7 that I have entered in blue letters are the column index to use as sort_colulmn in both SORT and SORTN formulas.
But in Query, other than this column index number, you can also use the column header aka column label. But none of them offers to sort a range by sort_column name aka field label.
For example, you want to sort the above sample data by the column_name “Country”. But by default, you must either use the number 2 or the column header C in Query for sorting the range B2
If we can directly use the sort_column name instead of the sort_column header in Google Sheets, it can bring some dynamism to the sort column. That means when you insert new columns or change the source data, it won’t break the already set sort column.
Here are some example formulas and in that, I am going to use the sort_column name instead of the sort_column header.
The Use of Sort_Column Name Instead of Sort_Column Header in Sort Functions
The data related to human poverty index, that I have used in the above illustration is imported to Google Sheets from this Wiki page. To test, I have imported the data as below.
- In cell B1, I’ve entered the URL copied from the source.
- In cell B2, used the below Importhtml formula.
=importhtml(B1,"table",2)So we have the data to sort by country name. Let me begin with the SORT function and then I will take you through the other two functions.
How to Use Sort_Column Name in the SORT Function in Google Sheets
Normal Sort Formula:
=sort(B3:H,2,true)In this formula, the column_index is 2 that is the country name column. I have omitted the header row in this formula as it may also get sortted.
If you want the header row included in the SORT formula in Google Sheets, you can use it as below. Using Curly Braces you can add the header row as the first row of the sorted data.
={B2:H2;sort(B3:H,2,true)}Now let me share the tip on how to sort by sort_column name in the SORT function in Google Sheets. See that cool formula.
={B2:H2;sort(B3:H,match("Country",B2:H2,0),true)}Yes! You must replace the sort_column index number by a Match formula. Inside that Match, you can use the sort column name.
In this formula, the column index number is dynamic in SORT because of the use of the column name as the index number.
Why it’s called dynamic SORT?
If the source data I mean the data to sort changes, the column index number may also change automatically. So you are not required to manually change this number in the sort as we are using column name not column index number.
I am using the SORTN function to sort my data. Can I apply the above dynamic sort column in that too?
Yes! You can follow the above same Match formula approach in SORTN too.
How to Get Dynamic Sort_Column in SORTN Function in Google Sheets
If you are not familiar with SORTN, I highly recommend you to learn that. Other than sorting and limiting the sort output to ‘n’ rows, it’s useful to eliminate duplicates.
Here I am just going to explain the dynamic column in sort part. I mean how to sort by sort_column name instead of the sort_column header in Google Sheets.
For other tips and tricks involving SORTN, just do a search on this blog using the search bar that’s placed on the top menu bar.
Regular SORTN:
={B2:H2;sortn(B3:H,9^9,0,2,true)}The SORTN with Dynamic Index Column Using Column Name:
={B2:H2;sortn(B3:H,9^9,0,match("Country",B2:H2,0),true)}You can only understand this formula if you know the SORTN use and tie modes in that.
SORTN(range, [n], [display_ties_mode], [sort_column], [is_ascending])Just try to understand that I have used the column_name (with the help of the Match formula) in the sort_column instead of using the column index.
Check my Google Sheets Function guide and there you can find the SORTN function. Hope you may spend some time to learn that first. It’s a powerful function similar to Query.
Another function that has sort capability is the Query. It’s very flexible to use for data manipulation in Google Docs Sheets.
Don’t think that it’s something very complicated to learn. You can learn it very easily. In my above-shared function guide, you can find Query too.
How to Sort Data by Sort_Column Name in Google Sheets Query
To sort, you can either use the column header or column index number as the identifier in Query. Here also you can use the Match formula to make it dynamic.
But do understand one important thing. We can’t use the Query in the above sample data! Do you want to know why?
The reason is simple. The mixed data type is always an issue with Query. The above data falls in that type.
See the column F. It contains, numbers and some other characters that makes it mixed type. You can sort out such issues in different ways. Here is one such tutorial – How to Solve the Mixed Data Type Issue in Query in Google Sheets.
So to learn how to use dynamic sort column in Query, I am using a ‘new’ dataset as below. I am going to copy the first few rows in the above data and paste it as values in another tab.
As you can see, this data does not contain any mixed column data.
Query Formula to Sort by Country Name Using Column Identifiers:
=query(B2:H,"Select * where B is not null order by C asc",1)Or
=query({B2:H},"Select * where Col1 is not null order by Col2 asc",1)Here in the second formula, we can incorporate the Match formula as below.
=query({B2:H},"Select * where Col1 is not null order by Col"&match("Country",B2:H2,0)&" asc",1)This way you can sort by sort_column name instead of sort_column header in SORT, SORTN, and QUERY in Google Sheets. Enjoy!
Additional Resources:
