XLOOKUP to return an array of ranges
Currently XLOOKUP can return an array of return values, given an array of search values, OR a range corresponding to a single search value. Like other functions, it does not return an array of ranges but, rather, truncates the calculation.
This may have been acceptable in the past when the expectation was to copy formulas across to return values that logically form an array. As dynamic arrays receive increasing use, the expectation will change and the failure of a range to spill will be far more problematic.
@Peter, I'm not seeing it on my build yet but it looks like arrays of ranges are going to be flagged up as #CALC! errors:
Nested arrays are quoted as 'not currently supported' which suggests there may be scope to support them in the future. I've added a related suggestion in the other post to be able to pass arrays in functions like SUMIFS which only accept ranges currently
Peter Bartholomew commented
Agreed. A non-volatile version that would also produce arrays of arrays that would feed into SUM, AND etc etc. would be the eventual goal. You might also like to comment on an ealier proposal
By all means put up counter proposals if you have better ideas. My aim was to introduce requirements but the approach is for others to determine.
Nice in principle but wouldn't this need changes to array calculation not just XLOOKUP function? For example INDIRECT and OFFSET can return array of ranges but show up as errors when entered in the grid
Wyn Hopkins commented
Mynda Treacy's video demo's the issue nicely here
Ed Hansberry commented
See this discussion in the Excel Tech Community for a specific example of what happens. https://techcommunity.microsoft.com/t5/excel-blog/announcing-xlookup/bc-p/830248/highlight/true#M708
Attached workbook will show how this works.