Dynamic INDIRECT with named ranges google sheets

Question

I want to union a few named ranges dynamically, while avoiding the use of Google Apps Scripts as much as possible. The number of named ranges can vary, so any references to them has to be dynamic. For instance:

Range citrics

citrics	quantity
orange	1
lemon	2
lime	3

Range berries

berries	quantity
blueberry	4
raspberry	5
strawberry	6

Usually, using INDIRECT will work as long as the function references just one named range.

For instance, =INDIRECT("citrics") will return the table citrics.

Now, when unioning ranges, apparently the only thing that works is to use curly brackets and individually specify the ranges to union, like ={INDIRECT("citrics");INDIRECT("berries")}

citrics	quantity
orange	1
lemon	2
lime	3
berries	quantity
blueberry	4
raspberry	5
strawberry	6

But if I plan to have a changing number of ranges, refering to them dynamically becomes impossible. I tried using TEXTJOIN like so =INDIRECT("{"&TEXTJOIN(";";TRUE;A13:A14)&"}") , where A13:A14 contains the values

citrics
berries

but all I get is Function INDIRECT parameter 1 value is '{citrics;berries}'. It is not a valid cell/range reference.

The frustrating part is that, even if I try to recreate a formula string that would essentially refer to the named ranges individually (for instance {INDIRECT("citrics");INDIRECT("berries")}), like so

=INDIRECT("{INDIRECT(" & """" & "citrics" & """" & ");INDIRECT(" & """" & "berries" & """" & ")}")

I still get the error from above.

Here's a sample spreadsheet with my attempts.

Is this impossible to do without using Google Apps Scripts?

Answer 1

You may try:

=reduce(tocol(;1);A13:A14;lambda(a;c;vstack(a;indirect(c))))