Help referencing most recent payment date

[JustSomeGuy]

Hello All,

Thanks in advance for whatever help comes my way.

I have been doing a lot of research on how to return the date of a most recent payment, unfortunately, my situation seems to be a little different than what the videos out there are showing.

Bottom line, I have a running daily column and jot down when payments come in and how much (in this instance, the amount doesn't matter).

Please see example and question below:

DATE PAYMENTS
9/1/2018 $10,000.00
9/2/2018
9/3/2018 $1,500.00
9/4/2018
9/5/2018 $7,000.00
9/6/2018
9/7/2018
9/8/2018 $6,500.00
9/9/2018
9/10/2018
9/11/2018
9/12/2018
9/13/2018
9/14/2018
9/15/2018

As stated above, my dates revolve year round, and go back to 2014, the sheet currently has over 1500 rows. I tried using a formula referencing the payment column with a function of ">0" but still couldn't get it to work properly.

Question: Given the data above, how do I have Excel tell me that the most recent payment was $6,500 and took place on 9/8/2018?

Thanks again for any help!

 

[jtakw]

Hi,

Don't know if there's more complexity in your Actual data, but this works for your sample shown:

Book1
	A	B	C	D	E
1	DATE PAYMENTS			Last pmt amount	Date
2	9/1/2018	10000		6500	9/8/2018
3	9/2/2018
4	9/3/2018	1500
5	9/4/2018
6	9/5/2018	7000
7	9/6/2018
8	9/7/2018
9	9/8/2018	6500
10	9/9/2018
11	9/10/2018
12	9/11/2018
13	9/12/2018
14	9/13/2018
15	9/14/2018
16	9/15/2018
Sheet266

Cell Formulas
Range	Formula
D2	=LOOKUP(9.99999999999999E+307,B2:B16)
E2	=LOOKUP(9.99999999999999E+307,B2:B16,A2:A16)

 

[JustSomeGuy]

Thank you very much!

 

[jtakw]

You're welcome.