Google Sheets Query Non-Contiguous Range

7
votes

Is it possible to write a query (inside a formula) that references non-adjacent (discontinuous, discontiguous, I don't know the proper phrase) ranges? For instance: =query(A2:C,E2:F,"Select Col2 where Col5=3")

I want to be able to put my query in Column D, so I wanted to split the range around it. If I just say "Query(A2:F..." it will detect circular dependency when the equation is in Column D.

If it helps, I have shared a spreadsheet with an example (highlighted in yellow):

2
google-sheets

2 Answers

14
votes

Try:

=query({A2:C,E2:F},"Select Col2 where Col5=3")

or if you don't want the label use:

=query({A2:C,E2:F},"Select Col2 where Col5=3 label Col2 ''")
1
votes

@Pawel Szczur Have you tried your formula with "\"?

=QUERY({s1!F1:F12115\s1!AH1:AH12115}, "SELECT Col1,Col2")