Forum Discussion
Newly updated Spill function broke my indexing formula
I use the formula =INDEX(Column I want value returned from,MATCH(Column with value to match,other column with value to match,0)) a lot in my work. Usually to return a value for the price stored in the same row with the part number I'm looking for. I use it to update pricing via CSV export/import for online stores from our excel price lists. It has always worked great. Now with the updated spill function it's just returning #SPILL in the cells. This has a massive impact on my work. Is there a way to turn off the feature or a different way to write the formula?
5 Replies
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!
Using your own notation, I believe you need to write the formula this way:
=INDEX(Column I want value returned from,MATCH(Cell with value to match,Column with value to match from,0),1)
Or:
=INDEX(B:B,MATCH(C1,A:A,0),1)where Col A holds part numbers, Col B prices and Col C will have the parts for which you want to match the prices.
