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

Meg Chhabria

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!

Nick Korna

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.

Nick Korna

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

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

Meg