Google Sheets ArrayFormula – Find Previous Non-Empty Cell and Last Row

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:
``````=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),))