Search column B for last two unempty field and get both of their column A counterparts

Question

I wish to find the last two payments in Column B (but not always above each other) and then obtain the date that is in column A for those payments. Then have the number of days between these payments shown in the top of Column B.

All information starts from row 10 downwards.

What formula could I use to get this information ?

Answer 1

Assuming that your data is formatted as an Excel table, put this formula into B1 and press CTRL+SHIFT+ENTER to make it a range formula.

=MAX(IF(Table1[Pmt]>0,Table1[Date]))-LARGE(IF(Table1[Pmt]>0,Table1[Date]),2)

You would obviously have to substitute the PMT and DATE entries to match the table column headings, and replace "Table1" with your actual table name.

Thanks to @simoco for the inspiration from Given value in column A, find min/max in column B in EXCEL

EDIT To reference from a different sheet, and to not use a table:

=MAX(IF(Sheet1!$D:$D>0,Sheet1!$A:$A))-LARGE(IF(Sheet1!$D:$D>0,Sheet1!$A:$A),2)

and press CTRL+SHIFT+ENTER to make it a range formula.