Table of Contents
Named ranges are a great tool to use when querying data in Google Sheets. Named ranges allow you to assign a name to a specific range of cells, making it easier to reference them in your queries. Queries can be written using the range name instead of its cell reference, making them easier to read and debug. Named ranges can also be used to share data across multiple sheets and workbooks, allowing for a more flexible and dynamic workflow.
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:
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.
The following tutorials explain how to perform other common operations in Google Sheets: