How to Query From Multiple Sheets using Query function

To query from multiple sheets using the Query function in Google Sheets, you can follow these steps:

1. Combine the data from the sheets using the ARRAYFORMULA or FILTER function.
2. Use the QUERY function on the combined data.

Example

Suppose you have two sheets named "Sheet1" and "Sheet2" with data in columns A and B. Here's how to query from both sheets:

Step 1: Combine the data from the sheets

We can use the ARRAYFORMULA or FILTER function to combine the data from both sheets.

Using ARRAYFORMULA:

In a new sheet or an empty cell, use the following formula to combine the data:

=ARRAYFORMULA({Sheet1!A:B; Sheet2!A:B})

This formula uses the curly brackets {} to create an array and the semicolon ; to stack the data from "Sheet1" and "Sheet2" on top of each other. The ARRAYFORMULA function processes the array.

Using FILTER:

Alternatively, you can use the FILTER function to combine the data:

={FILTER(Sheet1!A:B, NOT(ISBLANK(Sheet1!A:A))); FILTER(Sheet2!A:B, NOT(ISBLANK(Sheet2!A:A)))}

This formula uses the FILTER function to select only the non-empty rows from each sheet and combines them using the curly brackets {} and semicolon ;.

Step 2: Use the QUERY function on the combined data

Now that you have combined the data from both sheets, you can use the QUERY function to query the data. First, let's wrap the combined data formula in a QUERY function:

=QUERY(ARRAYFORMULA({Sheet1!A:B; Sheet2!A:B}), "SELECT * WHERE Col1 <> ''")

Or, if you used the FILTER function:

=QUERY({FILTER(Sheet1!A:B, NOT(ISBLANK(Sheet1!A:A))); FILTER(Sheet2!A:B, NOT(ISBLANK(Sheet2!A:A)))}, "SELECT * WHERE Col1 <> ''")

Now, replace the "SELECT * WHERE Col1 <> ''" part with your desired query. For example, if you want to select all rows where column B has a value greater than 100, you would use:

=QUERY(ARRAYFORMULA({Sheet1!A:B; Sheet2!A:B}), "SELECT * WHERE Col2 > 100")

Or, with the FILTER function:

=QUERY({FILTER(Sheet1!A:B, NOT(ISBLANK(Sheet1!A:A))); FILTER(Sheet2!A:B, NOT(ISBLANK(Sheet2!A:A)))}, "SELECT * WHERE Col2 > 100")

This will return a combined list of rows from both sheets where column B has a value greater than 100.