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

Applies To
Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for iPad Excel Web App Excel for iPhone Excel for Android tablets Excel for Android phones

The spilled array formula you're attempting to enter extends beyond the worksheet's range. Try again with a smaller range or array.

In the following example, moving the formula to cell F1 resolves the error, and the formula spills correctly.

Screenshot that shows #SPILL! error where =SORT(D:D) in cell F2 extends beyond the edges of the workbook. Move it to cell F1, and it works properly.

Common cause: Full column references

A common misunderstanding occurs when creating VLOOKUP formulas by overspecifying the lookup_value argument. Before dynamic array capable Excel, Excel only considered the value on the same row as the formula and ignored 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 change means that if you specify an entire column as the lookup_value argument, Excel attempts to look up all 1,048,576 values in the column. After it finishes, it tries to spill them to the grid, and it very likely reaches the end of the grid, resulting in a #SPILL! error.  

For example, when placed in cell E2 as in the following example, the formula =VLOOKUP(A:A,A:C,2,FALSE) previously looked up only the ID in cell A2. However, in dynamic array Excel, the formula causes a #SPILL! error because Excel looks up the entire column, returns 1,048,576 results, and reaches the end of the Excel grid.

Screenshot that shows #SPILL! error caused with =VLOOKUP(A:A,A:D,2,FALSE) in cell E2, because the results would spill beyond the worksheets edge. Move the formula to cell E1, and it works properly.

Use one of the following approaches to resolve this issue:

# Approach Formula
1 Reference just the lookup values you're interested in. This style of formula returns a dynamic array, but doesn't work with Excel tables.
Screenshot that shows 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 doesn't return a dynamic array.
Screenshot that shows use the traditional VLOOKUP with a single lookup_value reference: =VLOOKUP(A2,A:C,32,FALSE). This formula doesn't return a dynamic array, but you can use it with Excel tables. 		=VLOOKUP(A2,A:C,2,FALSE)
3 Request that Excel perform implicit intersection by using the @ operator, and then copy the formula down. This style of formula works in tables, but doesn't return a dynamic array.
Screenshot that shows use the @ operator, and copy down: =VLOOKUP(@A:A,A:C,2,FALSE). This style of reference works in tables, but doesn't 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

How to correct a #SPILL! errors

Dynamic array formulas and spilled array behavior

Implicit intersection operator: @