There are some more exotic reasons for why the #SPILL! error can occur. The previously mentioned reasons are the most common, but here are other reasons you may wish to investigate if the obvious fails.
Excel was unable to determine the size of the spilled array because it’s volatile and resizes between calculation passes. For instance, the following formula will trigger this #SPILL! error:
=SEQUENCE(RANDBETWEEN(1, 1000) )
Dynamic array resizes may trigger additional calculation passes to ensure the spreadsheet is fully calculated. If the size of the array continues to change during these additional passes and does not stabilize, Excel will resolve the dynamic array as #SPILL!.
Results Extend Beyond the Worksheet’s Boundary
For example, if we placed in cell B2 the following formula…
=VLOOKUP(A:A, A:C, 2, FALSE)
The results would cause a #SPILL! error because Excel will lookup the entire column, return 1,048,576 results, and hit the end of the Excel grid. So close; we only needed one more cell.
It’s generally considered poor practice to select entire columns when selecting ranges.
Spilled array formulas are not supported in Excel Tables. Spilled formulas should only exist in a single cell. Excel Tables will repeat the formula to every cell in the table’s column. This creates catastrophic interference between every cell in the column.
If you need to use the formula as a spilled array formula, you will need to revert the Excel table to a “plain table” using the Table Design (tab) -> Tools (group) -> Convert to Range option.
Out of Memory
The spilled array formula you’re attempting to enter has caused Excel to run out of memory. In these cases, try referencing a smaller array or range.
Spilling into Merged Cells
If the results of the spilled array encounter a cell that has been merged with other cells, a #SPILL! error will occur.
Unrecognized / Fallback
If Excel doesn’t recognize or can’t reconcile the cause of this error, make sure your formula contains all the required arguments for your scenario.