MicrosoftIn the main I would classify dynamic arrays and the associated functions, especially Lambda, as brilliant successes capable of revolutionising the art of spreadsheet from ground up. However, the failure to accommodate nested arrays is the elephant in the room. The more we build workarounds or, worse still, exploit the formula shortcomings, the harder it will be to correct the problem in the future; it is a massive compatibility problem in the making.
To be able to sort, accumulate, filter, split, lookup to return a dynamic 1D array is great, but why assume that there are no further instances of the same calculation sitting right next to the first? In my experience such calculations are rather like sheep; where you find one, it is almost certain to find other members of the flock nearby. The answer to such problems is blatantly obvious; it is a 2D array constructed in the same way as V/HSTACK but without the solution developer needing to trick the formula into returning the correct result.
At the moment there is a nightmare of inconsistency. Simply wrapping a working array formula with a further Lambda to make it more palatable can truncate the calculation; XLOOKUP fails to return a 2D array but INDEX/XMATCH works; TEXTSPLIT fails to work correctly on an list of separated text strings but MID will work on a similar list of fixed length text strings. The idea that BYROW and BYCOL fail the produce the correct results unless each row/column calculation just happens to return a scalar result (useful but limited) is ridiculous.
I would like to think I have been excessively strident in my criticism of what is otherwise an amazing achievement The worse possibility is that I might have understated the problems associated with leaving the new functionality logically incomplete.
