Excel: Finding previous non-zero value in column

Excel: Finding previous non-zero value in column

I'm trying to find a formula that can find the most recent non-zero value
in a column in Excel and call that value. For instance, the data may look
like:
A
1 0
2 0
3 5
4 0
5 0
6 1
7 0
In this data, if I were to apply the formula in the 7th row (on a third
separate column), I would want it to call the 1 in the second column and
6th row. If the formula were applied in a separate column in the 5th row
then I would expect it to call the 5 from the 3rd row.
Do let me know and thanks! I'd prefer if the answer was not in VBA!