How To Use Noncontiguous Ranges In QUERY Function: Google Sheets

Can you use noncontiguous ranges in the data parameter in a QUERY() function?

Yes, you can. Google Sheet’s QUERY() function permits the ability to use noncontiguous ranges, but they must be wrapped in set notation with curly braces {} . When applying a filter in the query parameter, you will need to use the ColX notation to reference the specific range according to its order in the set.

Recall that the QUERY formula contains a total of three parameters as follows:

=QUERY(data, query_statement, [number_of_header_rows])

The first parameter of the QUERY formula is the data being operated on, with the second parameter being the SQL-like statement for what is needed, and the third optional parameter is how many rows of headers to include. But how do you add multiple ranges to the first parameter?

To use noncontiguous ranges in a QUERY formula, wrap all ranges in the data set notation {} .

For example, QUERY({ A2:A, D2:D }, ...) gets the range of cells from range A2 to the bottom of the sheet and the range from D2 to the bottom of the sheet (etc.) and combines them as one data set for use in the QUERY function.

Note that if your locale has the comma as the decimal separator instead of separating each range in the set notation with a comma, you would need to separate each range with a backslash \ , for example, QUERY({ A2:A \ D2:D }, ...)

Here’s what this looks like in a Google Sheet:

1 John Smith 1 Lane St NSW John
=QUERY({A1:A, D1:D}, "SELECT *")
2 Jane Doe 2 Martin Pl NSW Jane NSW
3 John Doe 3 Query Lane NSW John NSW
Using multiple ranges in QUERY data parameter

How Do You Reference A Range In The Noncontiguous data Set?

If you have noncontiguous ranges in your QUERY formula by using the data set annotation above, how do you reference these in your query parameter?

To refer to a range in the noncontiguous set use the syntax ColX where X is the number of the column according to the order of ranges inserted into the first parameter.

For example, QUERY({ A2:A, D2:D }, …) would be referenced as Col1 when referring to the range in A2:A , and Col2 would refer to the range in D2:D .

Here’s a demonstration where I’m returning the Col1 data which is the range A2:A :

1 John Smith 1 Lane St NSW John
=QUERY({A1:A, D1:D}, "SELECT Col1")
2 Jane Doe 2 Martin Pl NSW Jane
3 John Doe 3 Query Lane NSW John
To reference each of the columns in the QUERY statement use Col followed by the column number (e.g. Col1 )

Be mindful when using the ColX annotation when referencing your ranges in the data parameter that you correctly capitalise Col – with a capital C at the beginning – case is important !

How To Handle Range Errors With Multiple QUERY Ranges

When using multiple ranges in your data parameter you may find you get a range error, why is this a problem?

Function ARRAY_ROW parameter 2 has mismatched row size. Expected: 3. Actual: 2.

QUERY formula error

When using multiple ranges in your data parameter, they need to contain the same length of rows.

For example, QUERY({ A1:A3, D1:D2 }, ...) will not work as the row height for both ranges is different. The first range has 3 rows, whereas the second range has 2 rows.

Make sure the row heights are the same.

1 John Smith 1 NSW #REF!
=QUERY({A1:A3, D1:D2}, "SELECT Col1, Col2")
2 Jane Doe 2 NSW
3 John Doe 3 NSW
Each range in the data parameter needs to be of the same row height, otherwise you will get a reference error.

Therefore, check the values inserted into your data parameter contain the same row height. If you are using functions such as SPLIT it returns values that span over columns, therefore, to convert that range to rows you need to wrap it with TRANSPOSE .


Using multiple noncontiguous ranges in a QUERY is possible provided each range is inserted in the first parameter, is wrapped in the data set notation {} , and all ranges imported have the same row height .

With the same techniques learned in this article, you can also concatenate ranges together to create your own data range . You could then apply filters and work on your own unique subsets of data with QUERY too.

Photo of author
Ryan Sheehy
Ryan has been dabbling in code since the late '90s when he cut his teeth exploring VBA in Excel. Having his eyes opened with the potential of automating repetitive tasks, he expanded to Python and then moved over to scripting languages such as HTML, CSS, Javascript and PHP.