How to use ARRAYFORMULA with OFFSET?

Question

Any thoughts why doesn't this work? I need the J column to be auto-populated. It works correctly if I just stretch down the formula. However I need it to dynamically adjust its size according to the number of elements in column G

Is that even achievable?

Answer 1

try:

=QUERY(H6:H, "offset 1", 0)

or in array:

=ARRAYFORMULA({G6:G, QUERY(H6:H, "offset 1", 0)})

---

UPDATE:

F3 cell:

=FILTER(B2:B; MOD(ROW(A2:A)-2; 4)=0)

G3 cell:

=FILTER(B2:B; MOD(ROW(A2:A)-3; 4)=0)

H3 cell:

=FILTER(B2:B; MOD(ROW(A2:A)-1; 4)=0)

I3 cell:

=FILTER(C2:C; MOD(ROW(A2:A); 4)=0)