How to Use Named Ranges in Google Sheets Queries

You can use the following syntax in a Google Sheets query to reference a named range:

=QUERY({my_named_range}, "SELECT Col1, Col3 WHERE Col1 = 'value1'")

This particular query will select the first column and third column from the named range called my_named_range where the first column in the named range is equal to ‘value1.’

Note: The named range must be wrapped in curly brackets, otherwise Google Sheets will throw an error.

The following example shows how to use this syntax in practice.

Example: Use Named Range in Google Sheets Query

Suppose we have the following dataset that contains information about various basketball players:

Notice in the Name box in the top left corner that this particular range of cells B1:D11 is named team_data.

We can use the following formula to select the first and third columns from this named range where the value in the first column is equal to “Mavs”:

=QUERY({team_data}, "SELECT Col1, Col3 WHERE Col1 = 'Mavs'")

The following screenshot shows how to use this query in practice:

Google Sheets query named range

Notice that the query returns the values in the team column and assists column where the value in the team column is equal to “Mavs.”

The benefit of using a named range in our query is that if the location of our original dataset is moved for any reason, the query will still work.

For example, suppose we shift our entire dataset one column to the left.

Our query with the named range will still work:

The query returns the same results as the previous example even though our cell range has been shifted.

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

Leave a Reply

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