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.