excelexcel-formula

Update the nth element of an array on Excel using formulas

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!

Solution

  • 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)

    Example