CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulas
Bobbie Kan HKMS
Bobbie Kan HKMS

Reputation: 17

Google sheets fill in the gaps using array formula

enter image description here

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

Upvotes: 0

Views: 653

Answers (1)

Mike Steelson
Mike Steelson

Reputation: 15318

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.

Upvotes: 1

Related Questions