MicrosoftI suspect you are right in that there is a touch of 'geeks corner' about the request. The problem may be that my suggestion attempts to straddle two objectives. The more restricted but understandable is to improve the XLOOKUP function to eliminate what might be seen as an unexpected fail.
[Thanks to Ed Hansberry for his example clarifying the point on UserVoice and to Wyn Hopkins for the link to Mynda 's new blog post which draws attention to the 'feature'].
The question I might ask of JoeMcDaid though is the background issue of "Is it possible for Excel to handle 'arrays of arrays' and 'arrays of ranges' in a generic way to turn what is now a problem into the basis for new functionality?" This is far more obscure but the payoff would be when
FUNCTION( array of arrays ) is evaluated, to give an
Array of FUNCTION( arrays ).
Given a table (say), that could be the SUM of each column, a conditional SUMIFS, other aggregations such as AND, (or even new functions such as UNIQUE to give a set of validation lists). Row sums would work in a similar manner but would require an array of rows to be the starting point. It all can be done now by manually copying the formula after each change to the referenced data but for the result to spill would be far better.
Now, that is geeky! 😞
