I have a spill error in Excel, and I want to replace this with an NA.
I tried using IFERROR, but this hasn't worked.
Is there another way around this ?
The Helper tab uses a lookup table to direct OFFSET and INDEX formulas used throughout, it only starts at Jan 2020 which is where the SPILL comes from.
From the comments (Daniel B) it seems SPILL isn't an actual error, hence IFERROR not working, but is there another way I can replace SPILL ?
Here is what is shown in cell B6
1 Answer
It seems Excel try to be too clever and fills down your formula creating an array formula.
This is a behaviour sometimes occurring in newer versions of Excel.
To clarify you want to use a single formula only, you can use @ (implicit intersection operator)
so your formula will become: ...@INDEX(...
