How do I combine the output of multiple QUERY functions?

Question

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?

share a copy/sample of your sheet with example of desired result — player0
– player0, Commented May 24, 2021 at 10:39

MattKing · Accepted Answer · 2021-05-24 16:17:23Z

2

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.

edited May 24, 2021 at 16:17

answered May 23, 2021 at 12:56

MattKing

7,80310 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

x-x Over a year ago

Thanks. I have updated the question to mention the query string is different for each sheet.

MattKing Over a year ago

@x-x I have updated my answer accordingly.

alexbogush · Accepted Answer · 2024-02-10 17:29:21Z

0

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
        )
      )
    )

answered Feb 10, 2024 at 17:29

alexbogush

211 bronze badge

Collectives™ on Stack Overflow

How do I combine the output of multiple QUERY functions?

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related