I have two columns in excel, one with a date and one with a rating 'low, medium, high'. I'm trying to write a formula to put in a third column that checks:
- If A2 = Low and B2 (date) is older than 12 months from today(), output
Overdue - If A2 = Medium and B2 (date) is older than 6 months from today(), output
Overdue - If A2 = High and B2 (date) is older than 3 months from today(), output
Overdue
If these parameters aren't met, output "Current".
This is what I have so far but I'm well aware its not right :) Could someone please point me in the right direction?
Thanks,
=IF(AND(A2="Low",LOOKUP(DATEDIF(B2,TODAY(),"m"),{0,3,6,12},{"Current", "Current" "Current","Overdue"}),(A2="Medium",LOOKUP(DATEDIF(B2,TODAY(),"m"),{0,3,6,12},{"Current","Current","Overdue","Overdue"}),(A2="High",LOOKUP(DATEDIF(B2,TODAY(),"m"),{0,3,6,12},{"Current","Overdue","Overdue","Overdue"})
CodePudding user response：
If you can use the excel 365 then I’d suggest creating an excel 
Here I named it IsCurrent. Then you just use that function where you want the output. e.g. 
CodePudding user response：
What I would suggest is to "outsource" the settings for the overdue months.
This is a good habit, as everyone looking at the table can see those settings and propably adjust them - without going into the formula.
And it is possible to use these settings in another formula :-)
If you use Excel 365 you can make the formula more readable/understandable with the LET-Formula.
=LET( OverdueMonthsForRating, IFNA(INDEX(configOverdue[Overdue months],MATCH([@Rating],configOverdue[Rating],0)),0), OverdueDate,IF(OverdueMonthsForRating>0, EDATE([@Date],OverdueMonthsForRating),TODAY()), IF(OverdueDate<TODAY(),"overdue","current") )
OverdueMonthsForRating is using a classic INDEX/MATCH to retrieve the number of months according to the Rating. In case Rating is not found 0 is returned
OverdueDate calculates - using EDATE - the overdue date based on the ratings date and OverdueMonthsForRating. In case Rating is not found TODAY is returned
Finally this date is evaluated against TODAY and the status is returned.
Classic Excel formula w/o LET:
=IF(EDATE([@Date],IFNA(INDEX(configOverdue[Overdue months],MATCH([@Rating],configOverdue[Rating],0)),TODAY()))<TODAY(),"overdue","current")