Spreadsheet: 852127d1702226191-n-a-issues-in-array-formula-also-need-help-in-if-condition-espacially-edate-coupon-maturity-dates-3-and-5-years
Sheet: Sheet1

S.No. | Customer Name | Investment Amount | Issue Date | 1st Coupon | 2nd Coupon | 3rd Coupon | 4th Coupon | 5th Coupon | 6th Coupon | 7th Coupon | 8th Coupon | 9th Coupon | 10th Coupon | Maturity Date | Bond Nature | Next Coupon Date | Tenor | [NAME] | Status | Tenor (Months)
1 | [NAME] | 1000000 | 2020-08-29 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  | 6-Months | Matured
 | Olivia | 5000 | 2020-12-31 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  | 3-Years | Outstanding
 | [NAME] | 5000 | 2021-05-31 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  | 5-Years | Outstanding
 | [NAME] | 100000 | 2021-08-30 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  | 3-Years | Outstanding
 | Sophia | 500000 | 2022-01-25 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  | 1-Year | Pre-Matured
 | [NAME] | 5000 | 2022-03-31 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  | 5-Years | Outstanding
 | [NAME] | 100000 | 2022-10-31 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  | 3-Years | Outstanding
 | [NAME] | 500000 | 2023-03-28 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  | 6-Months | Matured
 | Emma | 100000 | 2023-05-02 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  | 3-Months | Pre-Matured
 | [NAME] | 500000 | 2021-08-31 00:00:00 |  |  |  |  |  |  |  |  |  |  |  |  |  | 5-Years | Outstanding
 |  |  |  |  |  |  |  |  |  |  |  |  |  | 6
 |  |  |  |  |  |  |  |  |  |  |  |  |  | 36
 |  |  |  |  |  |  |  |  |  |  |  |  |  | 60
 |  |  |  |  |  |  |  |  |  |  |  |  |  | 36
 |  |  |  |  |  |  |  |  |  |  |  |  |  | 12
 |  |  |  |  |  |  |  |  |  |  |  |  |  | 60
 |  |  |  |  |  |  |  |  |  |  |  |  |  | 36
 |  |  |  |  |  |  |  |  |  |  |  |  |  | 6
 |  |  |  |  |  |  |  |  |  |  |  |  |  | 3
 |  |  |  |  |  |  |  |  |  |  |  |  |  | 60
 | STATUS
 | Outstanding | Hold by Customer till Maturity date |  |  | BLANK |  |  | Please help me in attached sheet to fix coupon due date if following situation occurs:
 | Matured | Matured at the date of Maturity |  |  | MATURED
 | Pre-Matured | Pre-Matured by Customer before Maturity date |  |  | Pre-Matured |  |  | Coupon Claims Date to be next date with the help of formula
 |  |  |  |  |  |  |  | January | No issue | July | No issue | Match day and month only not year
 | CLASSIFICATION |  |  |  |  |  |  | 31 days |  | 31 days
 | Resident |  |  |  |  |  |  | Febraury | Coupon of 29,30 & 31 August will be paid on 1st March (Next Date) | August | No issue | IF any day & month of these three dates then | 29-08-0000 | Give Coupon Date like this date | 01-03-0000
 |  |  |  |  |  |  |  |  |  |  |  |  | 30-08-0000
 | Non-Resident |  |  |  |  |  |  | days 28 |  | days 31 |  |  | 31-08-0000
 | Resident & Non-Resident |  |  |  |  |  |  | March | No issue | September | Coupon on 31st March will be paid on 1st Oct  (Next Date) | IF day & month of this date then | 31-03-0000 | Give Coupon Date like this date | 01-10-0000
 |  |  |  |  |  |  |  | days 31 |  | days 30
 | CURRENCY |  |  |  |  |  |  | April | Coupon of 31st Oct   will be paid on 1st May (Next Date) | October | No issue | IF day & month of this date then | 31-10-0000 | Give Coupon Date like this date | 01-05-0000
 | USD |  |  |  |  |  |  | days 30 |  | days 31
 | EUR |  |  |  |  |  |  | May | No issue | November | Coupon of 31st May will be paid on 1st Dec (Next Date) | IF day & month of this date then | 31-05-0000 | Give Coupon Date like this date | 01-12-0000
 | GBP |  |  |  |  |  |  | days 31 |  | days 30
 | AED |  |  |  |  |  |  | June | Coupon of 31st  Dec will be paid on 1st July (Next Date) | December | No issue | IF day & month of this date then | 31-12-0000 | Give Coupon Date like this date | 01-07-0000
 | SAR |  |  |  |  |  |  | days 30 |  | days 31
 | TENORS
 | 3-Months
 | 6-Months
 | 1-Year
 | 3-Years
 | 5-Years