This question is super old at this point and this is not a direct answer, but I figured I would chime in with a solution I found to a related issue which had initially led me here.

In my case I was trying to aggregate values located in the same cell across multiple sheets without using a reference column. Generating an array of all the sheet names is trivial enough, but then I was having a hard time figuring out how to actually use the values in those referenced cells, since even now in 2025 you can't use INDIRECT with array formulae.

What I ended up finding was that I could essentially substitute ARRAYFORMULA with MAKEARRAY. Use cases for this are going to be very niche and specialized so I don't have a great example of this in action that will be able to make this easier to grasp - but if you, like me, found yourself at this stackoverflow post, looking into the MAKEARRAY and required LAMBDA formulae might be where you find your solution too.