Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

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 or get support in Communities.

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?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×