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

formulasgoogle sheets

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.

Best Answer

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.