Skip to Content

How To Use Multiple Ranges In QUERY Function: Google Sheets

The QUERY formula is a great way of being able to perform basic SQL-like commands on a data range. The signature of the QUERY formula is 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 multiple ranges in a QUERY formula wrap the ranges in the data set notation {}.

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

ABCDEF
1JohnSmith1 Lane StNSWJohn
=QUERY({A1:A, D1:D}, "SELECT *")
NSW
2JaneDoe2 Martin PlNSWJaneNSW
3JohnDoe3 Query LaneNSWJohnNSW
Using multiple ranges in QUERY data parameter

How Do You Reference Multiple Ranges In QUERY?

If you have multiple ranges in your QUERY formula by using the data set annotation above, how do you reference these in your QUERY statement (the second parameter)?

To refer to a range when multiple ranges are being referenced in the QUERY formulas first parameter use the syntax Col followed by the column number according to the order of the 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 when referring to the range in D2:D.

ABCDE
1JohnSmith1 Lane StNSWJohn
=QUERY({A1:A, D1:D}, "SELECT Col1")
2JaneDoe2 Martin PlNSWJane
3JohnDoe3 Query LaneNSWJohn
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 Col annotation when referencing your ranges in the data parameter that you correctly capitalise Col – with a capital C at the beginning. The 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.

ABCDE
1JohnSmith1NSW#REF!
=QUERY({A1:A3, D1:D2}, "SELECT Col1, Col2")
2JaneDoe2NSW
3JohnDoe3NSW
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 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.