Google sheets fill in the gaps using array formula

February 8, 2022

I have this table and the date column has some blanks if the dates are the same.
i.e. B3:B6 are all 31/01/2022
I want to use column F to fill in the gaps.

I would like to use an arrayformula in cell F2 and it stops when the relative A is empty
i.e. if A279 is empty, F279 is empty too.

I tried to do it using a non-array solution
=IF(B2="",F1,B2)

and I cannot change it to array one
=ArrayFormula(IF(B2:B="",F1,B2:B))

I don’t know how to make F1 dynamic and the row number is always one less then B, like showing F2 if B3, F3 if B4

This is the Sheet

>Solution :

Try in F2

=ArrayFormula(lookup(row(A2:A),row(A2:A)/--(B2:B<>""),B2:B))

or (better) in F1

={"Date";ArrayFormula(if(A2:A="",,lookup(row(A2:A),row(A2:A)/--(B2:B<>""),B2:B)))}

to understand how it works, pls fill for instance in I2 =arrayformula(if(A2:A="",,row(A2:A)/--(B2:B<>""))) … if Bx is blank, we get an error, in that case lookup will take the previous value without error.