Override spill cell

Question

Is there a way to override spill cells without getting an error? Lets say in the A column I the cells contain some numbers in B1 I have "=A1:A10". Now I want to have a different value that I set only in B7. How can I override the value of this cell without getting a spill error?

Example:

Answer 1

A SpillRange will only spill if the entire range it's expanding to is empty -- no way around this. However, there are lots of ways to accomplish the same functional result by adjusting the formula to behave differently in that particular cell.

Here's a few ideas:

=IF(ROW(A1:A10)=7,"Something Else",A1:A10)`

I prefer to use the LET function to reduce the number of address references in a formula, even if it makes it longer, so I'd use:

=LET(zRange,A1:A10,IF(ROW(zRange)=7,"Let version Something Else",zRange))`

Overall something like Map Function seems most useful. I like including helper arrays as this:

=LET(keyRng, A1:A10, iRng, SEQUENCE(ROWS(keyRng),1),gResult,
    MAP(keyRng,iRng, LAMBDA(kVal,iVal,IF(iVal=7,"oh NO!!", iVal))),gResult)

Answer 2

The following will override and feed into multiple cells, however the solution posted above by Steven Sir, should work for your context in the OP, but here is another alternative:

=LET(α, A1:A10, IFERROR(MAP(IFNA(XMATCH(ROW(α),D2:D4),""),LAMBDA(r, CHOOSEROWS(E2:E4,r))),α))

---

Another alternative way could be using either VLOOKUP() or XLOOKUP()

=IFERROR(VLOOKUP(A1:A10,D2:E4,2,0),A1:A10)

---