The Named Ranges in Query in Google Sheets is a reality.

The Google Spreadsheet Database Query supports the use of Named Ranges in its SELECT and WHERE Clause.

Maybe this is the first time you are attempting to use the Named Ranges in Query.

If you have already tried, then you might have experienced a tough time coding your formula correctly.

With the help of this Query function tutorial, you can learn how to code a Query that includes named ranges.

How to Use Named Ranges in Query in Google Sheets – Examples

You are here because you already know the Named Ranges in Google Sheets and the use of Query.

I mean, you know to use both of them independently.

If not, don’t hesitate to check my relevant help articles below.

Short Description About Named Ranges

You can customize the name of a cell or cells using the Named Ranges in Google Doc Sheets.

For example, you can name the cell address A1 to “Hello” or a range of cells like A1:A10 to “Hellobaby.”

I’ve just used two funny names to name the cells.

But when you use it in real life, please use meaningful names like “sales,” “purchase,” “total,” etc.

Google Docs has official documentation on how to use the Named Ranges in its cloud-powered Spreadsheet Application.

Here is that source link.

I have my own version w.r.t. the use of this so-called Named Ranges in Google Sheets. See that also here – Make the Formulas Simple by Applying Names to Range of Cells.

Short Description About Query

The Query syntax is similar to the SQL.

This function helps users to perform various data manipulation techniques in Google Doc Sheets.

Here is my tutorial to learn using the Query function in Google Spreadsheets – Learn Query Function with Examples in Google Sheets.

Now coming back to our tutorial, i.e., how to use named ranges in Query in Google Sheets, I am beginning with the Select clause.

The Use of Named Ranges in Select Clause of Query

The Select clause is the option to select columns or reorder the columns in Query.

For example, in two-column data in the range A1:B, you can use the Query to return only the second column.

=QUERY(A1:B,"Select B")

You can replace column B here with a named range.

Read on to understand how to do that and why that is necessary.

Why Should One Use Named Ranges in Query Select Clause

To understand the benefit, insert a new column between your existing columns in the range and see that the column reference is the same in Query.

The “Select B” won’t become “Select C.” If you use named ranges, you can sort it out.

How to Use Named Ranges in Query Select Clause

To use named ranges to select a column in Query, you should use a helper cell. Here is one example.

Sample Data:

Now let’s see how to name a range for use in Query.

Steps:

1. In cell F1, type this formula.

=left(address(row(B1),column(B1),4))

I am naming column B.

If you want to name column A, change B1 to A1.

Formula Explanation

The ADDRESS formula, which includes the ROW and COLUMN functions, returns the cell address of B1.

The LEFT function extracts the first letter that is the column heading from it.

Update:- You can use the below REGEXEXTRACT instead of the above Left and Address combination as it (the latter combo) won’t return both the characters when you refer to cells such as AB1.

=REGEXEXTRACT(address(row(B1),column(B1),4),"[A-Z]+")

2. Click on cell F1 and then go to the menu Data > Named ranges. I am naming the range (here cell F1) as “Age.”

3. Here is the Query formula that includes a named range in the Select clause.

=QUERY(A:B,"Select "&Age)

I have this formula in cell D1.

 I am inserting a new column between A and B and see what happens to the formula result.

Example to Named Ranges in Where Clause in Query

Below you can learn the use of Named Ranges in the Query Where clause.

Here I am using a new set of data.

In this data, column A contains the first and last names in combined form.

To filter the data by the names in column A, I have entered the first name and last name (criteria) separately in E2 and F2.

So we will name the cells individually and then combine them in the formula.

Select E2 and name it “FirstName.” Similarly, name F2 as “LastName.”

=QUERY(A1:B,"Select * where A matches '"&join(" ",FirstName,LastName)&"'")

This Query formula will filter the rows that match the name “Sean Carter” in column A.

Similar: Matches Regular Expression Match in Google Sheets Query.

If there is only a single criterion, then the JOIN function is not required. The formula would be like this.

Change the value in cell E2 to “Amy Gonzalez” and use the below formula.

=QUERY(A1:B,"Select * where A matches '"&FirstName&"'")

The single and double quotes around the named range in this Query formula may change according to the criterion type (text, number, or date).

You can follow the above same approach if the criterion is a text string. If it’s a number, there is a difference in the use.

You can check my Function Guide and pick Query and see how to use different criteria in it.

By following the above examples, you can quickly learn how to use Named Ranges in Query in Google Sheets.

More Resources:

1. How to Increment Column in Query in Google Sheets.
2. How to Get Dynamic Column Reference in Google Sheets Query.
3. Dynamic H&V Named Range in Google Sheets.