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?
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives