Multiple conditions in in Sharepoint calculated column

Question

I am a bit new to SharePoint lists. I am trying to create a calculated column with multiple conditions as below.

So the priority needs to be automatically calculated based on other 2 columns Impact and Urgency. The numeric value below is the priority:

                   Urgency
Impact   High   Medium   Low
High      1       2       3 
Medium    2       3       4
Low       3       4       5

How to put a formula to automatically calculate Priority?

I tried with:

=IF(AND([Impact]="High",[Urgency]="High"),"1",IF(AND([Impact]="High",[Urgency]="Medium"),"2",IF(AND([Impact]="High",[Urgency]="Low"),"3",IF(AND([Impact]="Medium",[Urgency]="High"),"2",IF(AND([Impact]="Medium",[Urgency]="Medium"),"3",IF(AND([Impact]="Medium",[Urgency]="Low"),"4",IF(AND([Impact]="Low",[Urgency]="High"),"3",IF(AND([Impact]="Low",[Urgency]="Medium"),"4",IF(AND([Impact]="Low",[Urgency]="Low"),"5","")))))))))

The above is giving me error.

Answer 1

Use this formula:

=IF([Impact]="High", IF([Urgency]="High", "1", IF([Urgency]="Medium", "2", IF([Urgency]="Low", "3", ""))), IF([Impact]="Medium", IF([Urgency]="High", "2", IF([Urgency]="Medium", "3", IF([Urgency]="Low", "4", ""))), IF([Impact]="Low", IF([Urgency]="High", "3", IF([Urgency]="Medium", "4", IF([Urgency]="Low", "5", ""))), "")))

---

Note:

1. Sometimes comma(,) does not work in formula (I am not sure but it is based on something language or regional settings on your site). So in that case use semicolon(;) instead of comma(,).
2. Return your calculated field as Single line of text.

Official Documentations:

1. Calculated Field Formulas.
2. IF function.