You can use the following basic syntax to use a column name in a Google Sheets query:
=QUERY(A1:C11,"SELECT "&SUBSTITUTE(ADDRESS(1,MATCH("Team",A1:C1,0),4),1,""))
This particular query will select the column with the name “Team” in the cell range A1:C11.
The following example shows how to use this syntax in practice.
Example: Use Column Name in Google Sheets Query
Suppose we have the following dataset that contains information about various basketball players:
If we would like to select the “Team” column in an ordinary query, we would reference the column as A since it’s the first column in the range we’re interested in:
=QUERY(A1:C11, "SELECT A")
This would return just column A:
However, if we attempt to use “Team” as the column name then we’ll receive an error:
=QUERY(A1:C11, "SELECT Team")
Instead, we must use the following formula to select the “Team” column by name:
=QUERY(A1:C11,"SELECT "&SUBSTITUTE(ADDRESS(1,MATCH("Team",A1:C1,0),4),1,""))
This will return the “Team” column from the original dataset:
Note that you can use multiple SUBSTITUTE functions if you’d like to select multiple columns by name.
For example, you can use the following syntax to select both the “Team” and “Position” columns by name:
=QUERY(A1:C11,"SELECT "&SUBSTITUTE(ADDRESS(1,MATCH("Team",A1:C1,0),4),1,"")&","&SUBSTITUTE(ADDRESS(1,MATCH("Position",A1:C1,0),4),1,""))
The following screenshot shows how to use this formula in practice:
Notice that the query returns both the “Team” and the “Position” columns.
Additional Resources
The following tutorials explain how to perform other common operations in Google Sheets:
Google Sheets Query: How to Return Only Unique Rows
Google Sheets Query: How to Remove Header from Results
Google Sheets Query: How to Ignore Blank Cells in Query