#SPILL! error - Extends beyond the worksheet's edge

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.

#SPILL! error where =SORT(D:D) in cell F2 will extend beyond the edges of the workbook. Move it to cell F1, and it will work properly.

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.

#SPILL! error caused with =VLOOKUP(A:A,A:D,2,FALSE) in cell E2, because the results would spill beyond the worksheet's edge. Move the formula to cell E1, and it will function properly.

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

Use =VLOOKUP(A2:A7,A:C,2,FALSE) to return a dynamic array that won't result in a #SPILL! error.

=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.

Use the traditional VLOOKUP with a single lookup_value reference: =VLOOKUP(A2,A:C,32,FALSE). This formula will not return a dynamic array, but can be used with Excel tables.

=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.

Use the @ operator, and copy down: =VLOOKUP(@A:A,A:C,2,FALSE). This style of reference will work 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, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

FILTER function

RANDARRAY function

SEQUENCE function

SORT function

SORTBY function

UNIQUE function

#SPILL! errors in Excel

Dynamic arrays and spilled array behavior

Implicit intersection operator: @

Need more help?

Expand your Office skills
Explore training
Got It
Get instant Excel help
Connect to an expert now
Subject to Got It terms and conditions

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×