Find last value > 0 in range, return corresponding date in column A

Hi gurus,

I have a data set with dates in column A and corresponding payments in columns B onward.

I need a formula that finds the last payment (any value over 0) in a column and returns the date in cell A of the row which has the last payment.

Payments start at row 126 and currently end at row 827 (this will increase as a new row is inserted each day for new payments).

I would have the formula in the row directly underneath the last payment row (currently row 828), however this formula row would get pushed down 1 row each day so it needs to be able to adjust to that.

Example workbook attached.

Thanks in advance to anyone who takes the time to look at this!

So cell B828 should return the date 5/7/2013?

Try

=LOOKUP(9.99999999999999E+307,2/(B$1:B$829>0),$A$1:$A$829)

The key is making the range extend beyond your current last row.
As long as new data is inserted between rows 827 and 829, then the formula will auto adjust.

Try ...in column B828

=MAX(IF(B$126:B827>0,$A$126:$A827))

Enter with Ctrl+Shift+Enter


Copy across.

That's awesome, thanks Jonmo!

Tweeked it a little so that in row 829 it's

=TEXT(LOOKUP(9.99999999999999E+307,2/(B$1:B$828>0),$A$1:$A$828),"M/D/YYY")

You're welcome.

=index(a126:a897,max((row(indirect("1:"&counta(a126:a827))))*((b126:b827)>0)),1)