Multiple IF THEN outcomes based on combination of drop down values from 2 columns

I've looked through a number of formulas in this community, but can't seem to find one that works for my application. I am trying to build a Stakeholder Register sheet. Based on the values from dropdowns in 2 different columns, I want to populate a 3rd column with one of four stakeholder engagement strategies (Engage & Consult, Keep Satisfied, Keep Informed, Monitor).

Col 1 header label: Power Level (dropdown values can be blank, High or Low)

Col 2 header label: Influence Level (dropdown values can be blank, High or Low)

Col 3 header label: Engagement Strategy

Col 3 Formula needed:

If col 1= blank, regardless of value in col 2, return blank in col 3

OR

If col 2= blank, regardless of value in col 1, return blank in col 3

OR

If col 1= "High" AND col 2= "High", return "Engage & Consult" in Col 3

      OR

If col 1= "High" AND col 2 = "Low", return "Keep Satisfied" in Col 3

      OR

If col 1= "Low" AND col 2= "High", return "Keep Informed" in Col 3

      OR

If col 1 = "Low" AND col 2= "Low", return "Monitor" in Col 3

I've attached a visual to help with seeing expected outcomes.

Any help would be greatly appreciated!!

Answers

  • =IF([Power Level]@row = "", "", IF([Interest Level]@row = "", "", IF(AND([Power Level]@row = "High", [Interest Level]@row = "High"), "Engage & Consult", IF(AND([Power Level]@row = "High", [Interest Level]@row = "Low"), "Keep Satisfied", IF(AND([Power Level]@row = "Low", [Interest Level]@row = "High"), "Keep Informed", IF(AND([Power Level]@row = "Low", [Interest Level]@row = "Low"), "Monitor"))))))


    Are you wanting "BLANK" to be an available value? Depending on the answer, it will change the formula a little bit.

  • Thank you for this. To answer your question, yes I want column three to be left blank if values are missing in either column one or column two.

  • Sorry, should've clarified my question. For Power and Interest Level columns are you wanting to just leave the cell blank, or are you wanting to have "BLANK" as a selectable dropdown choice?