CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
x-x
x-x

Reputation: 7515

How do I combine the output of multiple QUERY functions?

I have the following formula...

=ARRAYFORMULA( VLOOKUP( QUERY(results_sheet_1:range, query_string_1), lookups_sheet:range, 11, FALSE))

... which returns a single column of values.

I need to call this formula on multiple sheets (results_sheet_1, _2, _3 etc) and combine the output in a single column. The query string is different for each sheet, though in all cases only a single column is returned.

How can this be done?

Upvotes: 1

Views: 832

Answers (2)

alexbogush
alexbogush

Reputation: 21

If you don't know upfront how many queries you will have, use the trick from this video, it allows to overcome obstacles of using array returning formulas within arrayformula by changing it to "reduce" formula.

Video: https://www.youtube.com/watch?v=QtIM0-bAVDg

Trick:

   reduce (
      "",
      filter(A1:A&"|"&B1:B),
      lambda(
        accumulator,
        current,
        vstack(
          NOT_ARRAYFORMULA_FRIENDLY_FUNCTION(
            SPLIT_IF_THERE_ARE_MULTIPLE_ARGUMENTS(current)
          ),
          accumulator
        )
      )
    )

Upvotes: 0

MattKing
MattKing

Reputation: 7773

Replace this:

...QUERY(results_sheet_1:range, query_string_1)...

with this:

{QUERY(results_sheet_1:range, query_string_1);QUERY(results_sheet_2:range, query_string_2);QUERY(results_sheet_3:range, query_string_3)}

Being sure to include the curly braces and semicolons between the ranges.

Upvotes: 2

Related Questions