An arrayformula to find the previous non-empty cell in another column and find the last row to work with

Question

I would like to do something similar to what was solved in the question:

An arrayformula to find the previous non-empty cell in another column

The problem was:

If I have a column "input", I would like the column "ouput" to be filled with the preceding value of the "input" column.

input - ouput a a a a b b b b b c c c c c

It was a great answer, however, the ArrayFormula works with an user-specified range:

=ArrayFormula(vlookup(ROW(1:10),{IF(LEN(A1:A10)>0,ROW(1:10),""),A1:A10},2))

How would I make the function to automatically get the last row that it has to take?

Specifically, I would like to know how to get automatically the number 10 of the last function.

I have tried to use INDIRECT but it finds an error.

=ArrayFormula(vlookup(ROW(INDIRECT("B3:B"&INDEX(SORT(B:B,ROW(B:B),FALSE),1))),{IF(LEN(INDIRECT("B3:B"&INDEX(SORT(B:B,ROW(B:B),FALSE),1))>0,ROW(INDIRECT("3:"&INDEX(SORT(B:B,ROW(B:B),FALSE),1)),""),INDIRECT("B3:B"&INDEX(SORT(B:B,ROW(B:B),FALSE),1)},2)))))

This part: INDIRECT("B3:B"&INDEX(SORT(B:B,ROW(B:B),FALSE),1)) is used to get the last value of a column.

I have created a spreadsheet with the functions: https://docs.google.com/spreadsheets/d/15jbXZbixRYxJSRQuKgBU8BWN6KYNjwwT5PzvYp986Ww/edit#gid=0

Any help would be appreciated.

Answer 1

Can't edit your spreadsheet, but this formula (entered in C3)

=ArrayFormula(if(row(B3:B) <= max(if(not(isblank(B3:B)), row(B3:B))),vlookup(row(B3:B),filter({row(B3:B),B3:B},len(B3:B)),2),))

should bring you the output you expected.

Answer 2

The following formula automatically fills the output column and stops at the row 10. To fill more rows, replace 10 by the desired number of rows or by a function / subformula that calculates the required rows.

=ArrayFormula(
   ARRAY_CONSTRAIN(
     VLOOKUP(ROW(A:A),{IF(LEN(A:A)>0,ROW(A:A),""),A:A},2),
     10,
     1
   )
 )