Forum Discussion
Newly updated Spill function broke my indexing formula
Don't give up and return to legacy approaches!
The spill error can arise because there is insufficient white space to output the spilt array or because you are attempting to use dynamic arrays from within an Excel Table. Assuming the latter and condensing your notation to
= INDEX( return_array, MATCH( lookup_value, lookup_array, 0 ) )
the formula can be reduced to a single lookup, which a table will accept, by inserting the '@' operator to give
= INDEX( return_array, MATCH( @lookup_value, lookup_array, 0 ) )
This formula will propagate down the table in the normal manner. Using structured reference notation this is the same as [@[Lookup_Value]] where the text now refers to the column header.
Since you have spill errors, you will also have access to the XLOOKUP function that largely replaces INDEX/MATCH and VLOOKUP,
= XLOOKUP( @lookup_value, lookup_array, return_array )
A further observation is that if the formulas are placed outside the table the formula can be allowed to spill.
Another way a generating a #SPILL! error is to use entire worksheet column references. If the formula is placed in row 1 it will return 1048576 values, just as you requested, but it is unlikely that this is your intention.
Peter, Dynamic Arrays and XLOOKUP are not deploying in parallel. Both of them are only on Insiders Fast channel.
Dynamic Arrays are additionally deployed for Monthly Targeted and partly for Monthly channels and Excel Online. No one of them so far has XLOOKUP.
Hello Segei
That is useful; I had rather lumped them together as 'dynamic array functionality'. I recently read a post on the release cycle, starting with the MS internal 'canary ring' (presumably named because a number of initiatives die when they are taken further down the mine). Are you aware of any information source on what is in the pipeline and its most likely deployment schedule?
I had intended to revert my licenses to corporate semi-annual but I cannot do that until at least basic dynamic array functionality is in place, I cannot visualise coping without it!
Peter, main source of information is Joe McDaid tweet. One of the latest is https://twitter.com/jjmcdaid/status/1199441292989587456?s=21. That's not about plans and pipelines, these are some latest news about deployment. Some later similar information could appear on Office Insiders site/twitter.
As for the plans practically the only information is Microsoft 365 roadmap https://www.microsoft.com/en-us/microsoft-365/roadmap?filters=, unfortunately it's practically nothing about the Excel.