How to find nearest non-blank cell above the current cell?

Question

I have a column of data, and I need to find the previous non-blank cell. For example, if I have the following data:

foo
-
-
-
-
(formula)

where - denotes a blank cell, then I want the (formula) cell to find a reference to the cell containing foo no matter how many blank cells are inserted between them. It is possible that new rows with blank cells in the test column could be inserted between the formula row and the previous non-blank row at any time, and the formula should be able to handle that.

Ideally, I would like to be able to put the formula in any cell on a row to find the nearest non-blank cell above that row in, say, column A.

An image to further illustrate my needs (and maybe elicit alternative ways to do what I want):

Answer 1

Use FILTER, ROW, MAX + ISBLANK() formulas to get it,

=FILTER(B1:B9, ROW(B1:B9)=MAX(FILTER(ARRAYFORMULA(ROW(B1:B9)), NOT(ISBLANK(B1:B9)))))

That does basically what you need. More precisely the below formula is "paste it anywhere" type as you asked, just paste the below formula on any cell and it will give you the last nonblank cell value.

=FILTER( INDIRECT(CHAR(64+COLUMN())&"1:"&CHAR(64+COLUMN())&ROW()-1) , ROW(INDIRECT(CHAR(64+COLUMN())&"1:"&CHAR(64+COLUMN())&ROW()-1))=MAX( FILTER( ARRAYFORMULA(ROW(INDIRECT(CHAR(64+COLUMN())&"1:"&CHAR(64+COLUMN())&ROW()-1))) , NOT(ISBLANK(INDIRECT(CHAR(64+COLUMN())&"1:"&CHAR(64+COLUMN())&ROW()-1))))))

Answer 2

If the formula is to go in A9, please try:

=index($A:$A,match("zzz",$A$1:$A9))