The spreadsheet user who posted this question has run into one of the most frustrating limitations in Excel's modern formula engine: the inability to produce an array of arrays. They are not alone, and their frustration is entirely justified. After years of incremental improvements, dynamic arrays, LET, LAMBDA, Excel still cannot handle the simple structural request at the heart of this post: "Give me one formula that outputs multiple arrays side by side." That is a genuine product gap, and pretending otherwise does users a disservice.
Let's look at what this user actually built. They wrote a LET-based formula that filters data by a unique ID, transposes the result, and then dragged it down a thousand rows just in case the dataset grows. The result is a column full of empty outputs and wasted computation. They tried BYROW, SCAN, and MAP, and found the Exceljet article confirming that the engine simply cannot nest spilled arrays. So they are stuck with either over-dragging or accepting brittle workbooks. For anyone who manages variable-sized data without VBA, this is a daily reality.
What this means in practice is that Excel's formula language still treats "one cell, one result" as the fundamental unit of work. Even with dynamic arrays, each formula is a single entry point. If you need to produce a matrix of results where each row is itself a variable-length array, you must either write a formula per row or use a helper structure like a table. The latter works, but it sacrifices the elegance of a single-cell solution. The user's workaround, dragging formulas down to row 1000, is the industry standard, and it is not good enough.
Our take is straightforward: Microsoft should prioritize a native "expand" or "array-of-arrays" operator in the formula language. The technical challenges are real, but the user demand is clear. Until that happens, the best practical advice for anyone in this situation is to embrace structured tables with implicit intersection, or to use Power Query for reshaping tasks. Neither is as clean as a single formula, but both avoid the garbage rows and recalc bloat. The user deserves better, and the spreadsheet community should keep asking for it.