Excel formula where certain combinations of cells would return a value-CodePudding

I'm creating a stakeholder map and I have two columns with two possible entries for each

Power | Interest | Strategy

Power and Interest can be either High or Low

So I am trying to get a strategy to return a value based on whether Power or Interest is one of those two combinations as an example:

Power = High, Interest = High, Strategy = Manage Closely
Power = Low, Interest = High, Strategy = Keep Informed
Power = Low, Interest = Low, Strategy = Monitor
Power = High, Interest = Low, Strategy = Meet Their Needs

Can someone help me with this formula?

CodePudding user response：

So, a long way is to use if() with AND() like so:

IF(AND(B1="high",D1="High"),"Manage closely",IF(AND(B1="Low",D1="High"),"Keep informed",IF(AND(B1="Low",D1="Low"),"Monitor",IF(AND(B1="high",D1="Low"),"Meet their needs"))))

But a table with vlookup() is easier to control as per the other answer. However, this does not use a helper column...

CodePudding user response：

Make a new column where you concatenate Power and interest.

  A     B           C            D
1 Power Interest    Concat       Result
2 High  Low         HighLow       
3 High  High        HighHigh

Then somewhere down in the excel (or in another tab) make a relationship between inputs and outputs

     A      B           C           D
1000 Power  Interest    Concat      Result
1001 High   High        HighHigh    Manage Closely
1002 High   Low         HighLow     Kep Informed
1003 Low    High        LowHigh     Monitor
1004 Low    Low         LowLow      Meet Their Needs

finally use the following formula in the cell D2 and slide it down to all your rows

=VLOOKUP(C2,$C$1001:$D$1004,2)

Where C is the concatenated column, C1001:D1004 is the relationship table location.

You can then hide the column C if you wish.

CodePudding user response：

I tried this formula and it worked for me:

=IF(AND(A2="High";B2="High");"Manage Closely"; IF(AND(A2="Low";B3="High");"Keep Informed"; IF(AND(A2="Low";B2="Low");"Monitor"; IF(AND(A2="High";B2="Low");"Meet Their Needs";0))))

Drag down/Copy and paste the formula under the Strategy Column and it should turn out like this:

CodePudding user response：

Another solution: You can use built-in function below after saving your file as an .xlsm file and saving the code in a new module in file:

    Function myStrategy(power As String, Interest As String) As String
    
    If LCase(power) = "high" And LCase(Interest) = "high" Then
    myStrategy = "Manage Closely"
    ElseIf LCase(power) = "low" And LCase(Interest) = "high" Then
    myStrategy = "Keep Informed"
    ElseIf LCase(power) = "low" And LCase(Interest) = "low" Then
    myStrategy = "Monitor"
    ElseIf LCase(power) = "high" And LCase(Interest) = "low" Then
    myStrategy = "Meet Their Needs"
    End If
    
    End Function