I’ve got an array on A1:An where n will change quite often. I’m trying to write a formmula that replaces the first and second element of the array for the values on cells F1 and F2, and also the nth and n-1th elements need to be replaced for the values in G1 and G2, keeping all the other elements without any changes.
I’ve tried to append arrays F1:F2 + A3:An-2 + G1:G2, but that was a real pain and nothing I’ve tried seems to work.
I’ve also tried to create an auxiliary array like {1,1,0,0,...0,0,0,1,1} to multiply and add stuff together, but that also proved to be pretty challenging.
The important thing, is that I want to be able to hold the new array in a formula, so that I can use it in array formulas.
Thanks!
3
With INDEX, as BigBen also suggested,
=LET(
a_n, A1:XLOOKUP(TRUE, A:A <> "", A:A, , , -1),
n, ROWS(a_n),
i, SEQUENCE(ROWS(a_n)),
IFS(
i <= 2,
INDEX(F1:F2, i),
i >= n - 1,
INDEX(G1:G2, 2 - (n - i)),
TRUE,
INDEX(a_n, i)
)
)
From what I understand (let me know if I got it wrong, so I can delete this answer and you can edit your post):
=LET(_Data,A:A,
_Start,F1:F2,
_End,G1:G2,
_LastVal,XMATCH(TRUE,_Data<>"",,-1),
_PickRow,(A2:INDIRECT("A"&_LastVal-2)),
_Stack,VSTACK(_Start,_PickRow,_End),_Stack)
1