The spilled array formula you're attempting to enter will extend beyond the worksheet's range. Try again with a smaller range or array.
In the following example, moving the formula to cell F1 will resolve the error, and the formula will spill correctly.
Common Causes: Full column references
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.
For example, when placed in cell E2 as in the example below, the formula =VLOOKUP(A:A,A:C,2,FALSE) would previously only lookup the ID in cell A2 . However, in dynamic array Excel, the formula will cause a #SPILL! error because Excel will lookup the entire column, return 1,048,576 results, and hit the end of the Excel grid.
There are 3 simple ways to resolve this issue:
|
# |
Approach |
Formula |
|---|---|---|
|
1 |
Reference just the lookup values you are interested in. This style of formula will return a dynamic array, but does not work with Excel tables.
|
=VLOOKUP(A2:A7,A:C,2,FALSE) |
|
2 |
Reference just the value on the same row, and then copy the formula down. This traditional formula style works in tables, but will not return a dynamic array.
|
=VLOOKUP(A2,A:C,2,FALSE) |
|
3 |
Request that Excel perform implicit intersection using the @ operator, and then copy the formula down. This style of formula works in tables, but will not return a dynamic array.
|
=VLOOKUP(@A:A,A:C,2,FALSE) |
Need more help?
You can always ask an expert in the Excel Tech Community or get support in Communities.
