2

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.

player0
  • 124,011
  • 12
  • 67
  • 124
kirkg13
  • 2,955
  • 1
  • 8
  • 12

1 Answers1

2

use:

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

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124