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​

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1		01-May-21	01-Jun-21	01-Jul-21	01-Aug-21	01-Sep-21	01-Oct-21	01-Nov-21
2	Ted	50.00	0.00	0.00	50.00	0.00	0.00	0.00	01-Aug-21
3	Fred	24.00	0.00	0.00	0.00	0.00	24.00	0.00	01-Oct-21
4	Buddy	36.00	36.00	36.00	36.00	0.00	0.00	0.00	01-Aug-21
5	Joe	78.00	78.00	78.00	78.00	78.00	78.00	78.00	01-Nov-21
6	Jeff	100.00	0.00	0.00	0.00	0.00	0.00	0.00	01-May-21
7	John	200.00	0.00	100.00	150.00	200.00	0.00	0.00	01-Sep-21
Data

Cell Formulas
Range	Formula
I2:I7	I2	=LOOKUP(2,1/(B2:H2>0),$B$1:$H$1)

 

[rigeljr]

Awesome, that worked! Can you explain the logic behind the formula?

 

[Fluff]

The lookup find the first cell right to left that is greater than 0 & return the relevant header value.

 

[rigeljr]

Is there a way to exclude the last two columns from the search or anything like that without having to adjust the formula each month?

 

[Fluff]

As long as the last two columns are either 0 or blank, there is no need to change anything.

 

[rigeljr]

What if the last two columns have numbers > 0?

 

[Fluff]

Surely if they are greater than 0 then that would be the last payment?

 

[rigeljr]

But because there are forecasts into the future it even goes into periods for which payments have not been collected yet.

 

[Fluff]

Then how do you decide which is the last column that should be looked at?