Forum Discussion
#SPILL! error - spill range is too big
#SPILL! error - Extends beyond the worksheet's edge
There is an often misunderstood method of creating VLOOKUP formulas by over specifying the lookup_value argument. Before dynamic array capable Excel, Excel would only consider the value on the same row as the formula and ignore any others, as VLOOKUP expected only a single value. With the introduction of dynamic arrays, Excel considers all the values provided to the lookup_value. This means that if an entire column is given as the lookup_value argument, Excel will attempt to lookup all 1,048,576 values in the column. Once it's done, it will attempt to spill them to the grid, and will very likely hit the end of the grid resulting in a #SPILL! error.
Hope I was able to help you with this info.
Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.