Find next payment date

[rallez]

Hi,

I have a spreadsheet with a set of dates and payments, each date does not mean there is a payment.
Each month I update a cell which contains the month (eg. April, May, June etc)
I want a formula to display the next payments' month.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Date of report[/TD]
[TD]April-18[/TD]
[/TR]
[TR]
[TD]Next payment date[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 970"]
<colgroup><col><col><col><col span="7"></colgroup><tbody>[TR]
[TD]Month[/TD]
[TD="align: right"]oct.-17[/TD]
[TD="align: right"]nov.-17[/TD]
[TD="align: right"]dic.-17[/TD]
[TD="align: right"]ene.-18[/TD]
[TD="align: right"]feb.-18[/TD]
[TD="align: right"]mar.-18[/TD]
[TD="align: right"]abr.-18[/TD]
[TD="align: right"]may.-18[/TD]
[TD="align: right"]jun.-18[/TD]
[/TR]
[TR]
[TD]Payment[/TD]
[TD="align: right"]50.000[/TD]
[TD="align: right"]350.000[/TD]
[TD="align: right"]120.000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]100.000[/TD]
[TD="align: right"]10.000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]400.000[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[rallez]

Index, match etc

 

[stunnrock]

Assuming the cell containing the month is A6, and the cells in B3:J3 are in date format, then:

=TEXT(INDEX($B$3:$J$3,MATCH(1,INDEX((MONTH($B$3:$J$3)>MATCH(LEFT($A$6,3),INDEX(TEXT(DATE(2000,ROW(INDIRECT("1:12",1)),1),"mmm"),),0))*(YEAR($B$3:$J$3)>=YEAR(TODAY()))*($B$4:$J$4>0),),0)),"mmm")

will give you the next month that contains a payment larger than 0. E.g. if you enter April, or Apr in A6, the answer for your data is Jun.