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:
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | John | Smith | 1 Lane St | NSW |
John
=QUERY({A1:A, D1:D}, "SELECT *")
|
NSW |
| 2 | Jane | Doe | 2 Martin Pl | NSW | Jane | NSW |
| 3 | John | Doe | 3 Query Lane | NSW | John | NSW |
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
:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 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 |
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.
QUERYformula 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.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | John | Smith | 1 | NSW |
#REF!
=QUERY({A1:A3, D1:D2}, "SELECT Col1, Col2")
|
| 2 | Jane | Doe | 2 | NSW | |
| 3 | John | Doe | 3 | NSW |
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.
