EN VI

Is there a way to merge a variable number of ranges in an array together in Excel/Sheets?

I have two arrays. One contains string values referring to other ranges of the sheet (other pages). The ranges that the strings are referencing have a constant column size (horizontal, 4 in this case) but may differ in row size (vertical). The other array contains a list of names. Both arrays are the same size, with positioning corresponding to each. I want to obtain a longer range from this data, with the names of the second array to the right of each range respectively. Preferably with a single function/cell, spilling over to the space required. I would extremely dislike to have to use google appscript/vba script. Layout of the problem:

MainPage:

Reference	Name
FirstPage!A2:D4	Bob
SecondPage!A3:D5	Adam

FirstPage:

row#	Id	Amount	Goods	Price
2	23	45	Celery	120$
3	12	34	Radish	100$
4	8	32	friedegg	50$

SecondPage:

row#	Id	Amount	Goods	Price
3	35	23	Lettuce	32$
4	10	64	Milk	87$
5	9	95	cpus	234$

Wanted Output:

row#	Id	Amount	Goods	Price	Name
2	23	45	Celery	120$	Bob
3	12	34	Radish	100$	Bob
4	8	32	friedegg	50$	Bob
5	35	23	Lettuce	32$	Adam
6	10	64	Milk	87$	Adam
7	9	95	cpus	234$	Adam

What I have tried:

lambda(id, MAKEARRAY(rows(indirect(index(MainSheet!A2:A999, id))), 5, LAMBDA(row, column, if(column=5, index(MainSheet!B2:B999, id), index(INDIRECT(index(MainSheet!A2:A999, id)), row, column)))))(1)

\-> works for individual ranges, but I can´t figure out how to merge them (hence the question title) (just replace the 1 with the id of the reference)

VSTACK(makearray(counta(MainSheet!A2:A999), 1, lambda(id, _, MAKEARRAY(rows(indirect(index(MainSheet!A2:A999, id))), 5, LAMBDA(row, column, if(column=5, index(MainSheet!B2:B999, id), index(INDIRECT(index(MainSheet!A2:A999, id)), row, column)))))))

\-> "result should be in a single row" error

reduce({},makearray(counta(MainSheet!A2:A999), 1, lambda(id, _, MAKEARRAY(rows(indirect(index(MainSheet!A2:A999, id))), 5, LAMBDA(row, column, if(column=5, index(MainSheet!B2:B999, id), index(INDIRECT(index(MainSheet!A2:A999, id)), row, column)))))), LAMBDA(array, array2, array&array2))

\-> "Reference error": it seems that reduce() doesn't work with arrays as an initial value. (I've also tried forcing it with arrayformula()

Solution:

Use REDUCE to iterate A2:A3 and get the references
OFFSET to get the corresponding name,
INDIRECT to get the corresponding range from the given reference,
IF/SEQUENCE to create duplicate names according to the number of ROWS in the range
STACK the range and names Horizontally and finally Vertically stack the current iterator result with the previous accumulator.

=ARRAYFORMULA(
  REDUCE(
    SPLIT("Id,Amount,Goods,Price,Name",","),
    A2:A3,
    LAMBDA(a,c,
      LET(
        ref, c,
        name, OFFSET(c,0,1),
        range, INDIRECT(ref),
        names, IF(SEQUENCE(ROWS(range)),name),
        VSTACK(a,HSTACK(range,names))
      )
    )
  )
)