How to query multiple sheets and merge the data?

Let me else suggest using {} to collect all data:

Sample formula for 3 sheets:

=QUERY({'1'!A1:D13;'2'!A2:D13;'3'!A2:D13},"select Col1, Col2")

Here we use header only once in worksheet #1: range '1'!A1:D13 contains header, range '2'!A2:D13 and others don't. Also we replace A, B, C notation with Col1, Col2, Col3...

Look at example workbook, may be someone'll find it hefpful.


This doesn't solve the merge part of your problem, but an efficient way of querying all the sheets is:

=QUERY(Indirect(A1); "select A, D where C != ''")

where cell A1 has the text value Mona!A3:U300

With this method you can list the different sheet names in cells and have a lookup for each one.

It might be possible to use an ArrayFormula to combine the queries into one formula, though I'm not sure how well query would work for this - using either Sumproduct or Filter to look up values using multiple criteria might work better.