How to Use Column Names in Google Sheets Query


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:

Google Sheets query column name

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

4 Replies to “How to Use Column Names in Google Sheets Query”

  1. Thanks for sharing!

    Quick question, does this help in referencing a specific column when the column sequences changes? E.g. using your sample data, if you added a new column for “Coach” in column A, and “team”moved to column B, would the formula return the “team name” or “coach name”?

    What I’m trying to establish is how to use absolute column references in google sheets query, similar to what IndexMatch does. Thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *