VLOOKUP or INDEX/MATCH Question

[rigeljr]

Hi all,

I am looking for some help with the below table today. This is sample data that represents some real data that I have. The dollar amounts represent payments that have been made and $0 amounts indicate that no payment was made. What I want to do is be able to find the date of the last payment made for each of the people. So for example, Ted would be 1-Aug-21, Fred would be 1-Oct-21, Buddy would be 1-Jul-21 and so on...

Would appreciate any help with this, thank you!

	1-May-21​	1-Jun-21​	1-Jul-21​	1-Aug-21​	1-Sep-21​	1-Oct-21​	1-Nov-21​
Ted	$50.00​	$0.00​	$0.00​	$50.00​	$0.00​	$0.00​	$0.00​
Fred	$24.00​	$0.00​	$0.00​	$0.00​	$0.00​	$24.00​	$0.00​
Buddy	$36.00​	$36.00​	$36.00​	$36.00​	$0.00​	$0.00​	$0.00​
Joe	$78.00​	$78.00​	$78.00​	$78.00​	$78.00​	$78.00​	$78.00​
Jeff	$100.00​	$0.00​	$0.00​	$0.00​	$0.00​	$0.00​	$0.00​
John	$200.00​	$0.00​	$100.00​	$150.00​	$200.00​	$0.00​	$0.00​

 

[rigeljr]

Date has to equal the first of the current month

 

[Fluff]

In that case how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1		01/05/2021	01/06/2021	01/07/2021	01/08/2021	01/09/2021	01/10/2021	01/11/2021
2	Ted	50	0	0	50	0	0	0	01/05/2021
3	Fred	24	0	0	0	0	24	0	01/05/2021
4	Buddy	36	36	36	36	0	0	0	01/07/2021
5	Joe	78	78	78	78	78	78	78	01/07/2021
6	Jeff	100	50	0	0	0	0	0	01/06/2021
7	John	200	0	100	150	200	0	0	01/07/2021
Done

Cell Formulas
Range	Formula
I2:I7	I2	=INDEX($B$1:$H$1,AGGREGATE(14,6,(COLUMN($B$1:$H$1)-COLUMN($B$1)+1)/($B$1:$H$1<=TODAY())/($B2:$H2>0),1))

 

[rigeljr]

Perfect! Thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.