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)

should bring you the output you expected.