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

CodePudding user response：

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.