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:

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:

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:

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.

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.

Summary

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.