How to combine two ranges in one QUERY in Google Sheets
Google Sheets formula
=QUERY({A1:C6;E2:G6},"select Col1, Col3 order by Col3 desc",1)Stack the ranges in curly braces with a semicolon and refer to columns as Col1, Col2: =QUERY({A1:C6;E2:G6},"select Col1, Col3 order by Col3 desc",1).
- Result on the example
- a 11-row list that spills from I1 in
I1 - Excel
- See below
See it work
The formula is already in I1. Change any input and the result updates, or edit the formula to experiment.
How it works
{A1:C6;E2:G6} builds one array: the semicolon puts the second range under the first (a comma would put it beside). The first range includes the header row, so the headers argument is 1. Because the data is now an array rather than a sheet range, the columns are called Col1, Col2 and Col3 instead of letters.
=QUERY({A1:C6;E2:G6},"select Col1, Col3 order by Col3 desc",1)Result{11 rows × 2 cols} "Rep", "Revenue", "Eve Lund", 57800, …
A1:C66 rows × 3 cols: "Rep", "Deals", "Revenue", "Ana Ruiz", …A range: 6 rows × 3 columns.
E2:G65 rows × 3 cols: "Raj Patel", 9, $51,750, "Mo Diaz", …A range: 5 rows × 3 columns.
{A1:C6;E2:G6}{11 rows × 3 cols} "Rep", "Deals", "Revenue", "Ana Ruiz", …An array of values.
QUERY({A1:C6;E2:G6},"select Col1, Col3 order by Col3 desc",1){11 rows × 2 cols} "Rep", "Revenue", "Eve Lund", 57800, …QUERY: Runs a SQL-like query: select, where, group by, order by, limit, label.- data
{A1:C6;E2:G6}{11 rows × 3 cols} "Rep", "Deals", "Revenue", "Ana Ruiz", … - query
"select Col1, Col3 order by Col3 desc" - headers
1
- data
In Excel
=SORT(CHOOSECOLS(VSTACK(A2:C6,E2:G6),1,3),2,-1).Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =QUERY(VSTACK(A1:C6,E2:G6),"select Col1, Col3 order by Col3 desc",1) | spills 11 × 2 | VSTACK does the same stacking and reads more clearly. |
| =QUERY({A2:C6;E2:G6},"select Col1, Col3 where Col3 > 45000 order by Col3 desc",0) | spills 6 × 2Different result | Without the header row, set headers to 0. Here with a where clause added. No header, and only revenue over 45,000. |
Common mistakes
- Column letters don't work on an array:
select A, Creturns an error. Use Col1, Col3. - Both ranges need the same number of columns, or the array fails with an error about mismatched sizes.
- In some locales the separator for side-by-side columns is a backslash,
{A1:A6\C1:C6}. The semicolon for stacking is the same everywhere.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Combine two ranges in one QUERY
I1, use one QUERY over both team tables (A1:C6 and E2:G6) to list every rep and their revenue, largest first, with one header row.Questions
How do I query ranges from two sheets?
Use sheet references inside the braces: ={'East'!A2:C;'West'!A2:C}, and add where Col1 is not null to drop empty rows.
Can I query data from another file?
Yes, with IMPORTRANGE inside QUERY. Columns are again Col1, Col2.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.