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

Options

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

  • Smartsheet User 99
    Options

    =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.

  • Dawnk
    Options

    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.

  • Smartsheet User 99
    Options

    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?