Set Priority based on Due Date(s)

Hello,

I am attempting to define an issue priority based on the assigned due date. Below is the IF Statement that I am using. It works fine for the "Critical" (<=+5) and "Low" (>+30) as these do not include ranges. I am having issues on the "High " and "Medium" values. They will work fine if the due date is exactly 15 days from Today (High) and 30 days (Medium) My question is how do I write the formula so that it returns the "High" value for all dates that are >5 and <= to 15 days as well as a Medium value for all days that are >15 and<30 days from due date?

=IF(I15=TODAY()+5,"Critical",IF(I15=TODAY()+15,"High",IF(I15=TODAY()+30,"Medium",IF(I15>TODAY() +30,"Low"))))

Finally, once I get this working, I want to have a formula that will provide a total count based on current priority.

Any help or guidance would be greatly appreciated.

Thank you

Thank you for the quick reply. I have applied this and it works perfectly for any date in the future. However, If a Due date has passed I would like to have it return a "Critical" value. So any Date < =Today() +5 should return a "Critical" value. What needs to be completed to have this work?

Also, can you assist with the formula to return the total number of items based on current priority ?

Thank you again,
Tim

Originally Posted by mehmetcik

=LOOKUP(D6-TODAY(),{-120,5,10,15,30},{"Critical","Critical","High","Medium","Low"})

Hello, one last question. I am building this out in a template and was looking to see if is it possible to embed an IF Blank statement so that if the cells (D6) is blank the cell does not populate with the #N/A value and instead is blank?

Thank you