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.

Date of report	April-18
Next payment date
Month oct.-17 nov.-17 dic.-17 ene.-18 feb.-18 mar.-18 abr.-18 may.-18 jun.-18 Payment 50.000 350.000 120.000 0 0 100.000 10.000 0 400.000 <colgroup><col><col><col><col span="7"></colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[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.