Which formula to use to set conditional rules between 3 columns in a sheet?

Options

Using formulae in sheets, I am trying to create a template for Stakeholder mapping by embedding the below conditions and results. For Instance If (1) is entered in Column A and (B) is entered in column B, column C should pull in "Keep Informed". Or if (2) is entered in column A and (C) is entered in column B, column C should pull in "Regular Contact.

Here are the conditions:

1B, 1C = Keep Informed

1A = Manage Closely

2C, 2B, 3C, 3B = Regular Contact

2A, 3A = Anticipate and Manage Needs

Where,

1= Most Interest, 2=Some Interest and 3=Least Interest

A=Most Influence, 2=Some Influence and C=Least Influence


I have started by creating these using the IF function (see screenshot below) but need help to ensure that these conditions are pulled into all the column cells in the sheet. Therefore, depending on what someone enters in those columns, the correct result is pulled in.

TIA!

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Meg Chhabria,

    I think you're after a nested IF statement to display the outcomes listed. If you enter this formula in your Engagement column and convert to column formula it should work:

    =IF(AND(CONTAINS("(1)", Interest@row), CONTAINS("(A)", Influence@row)), "Manage Closely", IF(AND(CONTAINS("(1)", Interest@row), OR(CONTAINS("(B)", Influence@row), CONTAINS("(C)", Influence@row))), "Keep Informed", IF(AND(CONTAINS("(A)", Influence@row), OR(CONTAINS("(2)", Interest@row), CONTAINS("(3)", Interest@row))), "Anticipate and Manage Needs", IF(AND(OR(CONTAINS("(2)", Interest@row), CONTAINS("(3)", Interest@row)), OR(CONTAINS("(B)", Influence@row), CONTAINS("(C)", Influence@row))), "Regular Contact", ""))))

    I've used the CONTAINS and number/letter values to try and keep it a bit shorter, but you should end up with a result like this:

    If your Interest/Influence columns are dropdowns then you shouldn't have any exceptions. If not, then other results will come up blank in Engagement if they don't meet any of the criteria.

    Hope this helps, and if you've any questions just ask.

Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @Meg Chhabria,

    I think you're after a nested IF statement to display the outcomes listed. If you enter this formula in your Engagement column and convert to column formula it should work:

    =IF(AND(CONTAINS("(1)", Interest@row), CONTAINS("(A)", Influence@row)), "Manage Closely", IF(AND(CONTAINS("(1)", Interest@row), OR(CONTAINS("(B)", Influence@row), CONTAINS("(C)", Influence@row))), "Keep Informed", IF(AND(CONTAINS("(A)", Influence@row), OR(CONTAINS("(2)", Interest@row), CONTAINS("(3)", Interest@row))), "Anticipate and Manage Needs", IF(AND(OR(CONTAINS("(2)", Interest@row), CONTAINS("(3)", Interest@row)), OR(CONTAINS("(B)", Influence@row), CONTAINS("(C)", Influence@row))), "Regular Contact", ""))))

    I've used the CONTAINS and number/letter values to try and keep it a bit shorter, but you should end up with a result like this:

    If your Interest/Influence columns are dropdowns then you shouldn't have any exceptions. If not, then other results will come up blank in Engagement if they don't meet any of the criteria.

    Hope this helps, and if you've any questions just ask.

  • Meg Chhabria
    Options

    @Nick Korna, thank you so much! This was definitely helpful :)

    Meg

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!