How to use INDIRECT (or alternative) in an Array into a Matrix

Question

I want to merge multiple arrays that are in multiple sheets into a single matrix with the size of Max(Columns(arrays)) x Count(arrays). See image bellow to better understand the inputs and expected results.

Input values and expected results image

https://www.mediafire.com/file/g832c50izpqrnu5/IndirectErrorExample.xlsx/file

The expected result is the last one, recreated with HSTACK. However, this method is executed by brute forcing all different inputs and is not dynamic.

The second example is just to show that the INDIRECT formula works individually.

Lastly, the first example is how I tried to execute, calling a transposed array as input and expecting a matrix output.

Is there something I can do to fix this #VALUE! error in this case? Or is there an alternative?

Answer 1

REDUCE/HSTACK With INDIRECT

=IFNA(DROP(REDUCE("",A1:A3&"#",LAMBDA(rr,r,
    HSTACK(rr,INDIRECT(r)))),,1),"")

Edit

• Mayukh Bhattacharya suggested this, IMO, improvement:

  =DROP(IFNA(REDUCE("",INDIRECT(A1:A3&"#"),
      LAMBDA(rr,r,HSTACK(rr,r))),""),,1)
  

  Note how IFNA needs to go after DROP to be applied to a smaller array (range).