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

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

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

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

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

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

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

Stakeholder Register Engagement.jpg

Any help would be greatly appreciated!!

Find more posts tagged with

Accepted answers

All comments

Smartsheet User 99

=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

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

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?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.