How do I fill down a column, copying the cell above, unless the adjacent cell has a value, then copy that value?

Question

I'm trying to clean up a data table, and I want to fill column B with values, based on the last non-blank value in column A.

This formula works, if copied down in column B, but breaks whenever I insert a blank row, which I will need to do. I'm hoping for an arrayformula that will do the trick, but haven't found it yet.

My sample sheet is here. https://docs.google.com/spreadsheets/d/1q73uLmSCFwt1XnWEDOizf9i9uYDfFqXyr_VZ74DOU3c/edit?usp=sharing

I've tried variations of the following formulae:

=ArrayFormula(if(A3:A12="",indirect("B"&row()-1),A3:A12))
=ArrayFormula(if(A3:A12="",offset(B3:B12,-1,0,1,1),A3:A12))
=ArrayFormula(if(A2:A12="",offset(B2:B12,-1,0,1,1),A2:A12))

Many thanks for any guidance.

Answer 1

use:

=ARRAYFORMULA(VLOOKUP(ROW(A2:A12), IF(A2:A12<>"", {ROW(A2:A12), A2:A12}), 2, 1))