Learn to easily fix an XLOOKUP #SPILL Error in Excel. Chances are if you have been working with Dynamic Array Formulas in Excel, you have run across a Spill Error. Basically, this error occurs when the spill range is blocked.

Look at the example below. Here you can see that when the XLOOKUP formula is copied down using the fill handle, it returns a Spill error.

## Fix XLOOKUP #SPILL Error in Excel

One simple way to fix this error is by adding an “@” before the formula.

=@XLOOKUP(value,range1,range2)

By adding the “@” sign before the formula, Excel will enable Implicit Intersection which many value are limited to a single result. This prevents Excel from showing the #SPILL Error message.

Gagandeep GoyalNot able to fix spill error in below formula by applying @ before Xlookup

=XLOOKUP($C3,pivots!$A9,XLOOKUP(‘Final Summary’!F$2,pivots!$B$8:$D$8,pivots!$B$9:$D$14,0,0,1))

ExcelbudI’ll look at the formula and get you a solution.

Anwar AhmedIt worked, thanks a lot

Deepak JaniThanks. U helped me a lot

AdamSFixed my problem!

AdamSAnd then it didn’t lol….

ArpitaBy adding @ now I am getting #Value error

ExcelbudCan you provide your formula?

JohnDoe=IFERROR(XLOOKUP($D43,’FA info’!$C:$C,’FA info’!$F:$R,XLOOKUP(H$36,’FA info’!$8:$8,’FA info’!$12:$1485)),0)

Original formula above. @ before both XLOOKUPS not working for me

AmeenWorked perfectly. many many thanks genius