Change the drop down values, based on selection in other columns(drop downs)

**zeebee** · 10-18-2017, 12:23 PM

I am trying to create a template for Risk Analysis Matrix. I have 3 drop downs in 3 columns, Column A Called "Risk Severity" with values (ACCEPTABLE, TOLERABLE,UNDESIRABLE,INTOLERABLE). Column B called "Risk Likelihood" with values (IMPROBABLE,POSSIBLE,PROBABLE) and my last column C is a drop down with "Risk Level" with values (LOW,MEDIUM,HIGH,EXTREME). What I want is based on the selection the user makes in column A"Risk Severity" and Column B "Risk Liklihood" to change the value in column C drop down.

For E.g If A = Acceptable and B= Improbable then C should be Low. If A = Acceptable and B= Possible then C should be Low IF A = Intolerable and B= Probable then C should be extreme so on and so forth.

All in all I will end up with 12 combinations(4*3) and 4 different values in column C based on those 12 combinations.

Thanks in advance for your help

**mehmetcik** · 10-18-2017, 01:16 PM

If A2 is your dropdown for Risk Severity and B2 is your dropdown for Risk Liklihood

then put this formula in C2

Formula:

=CHOOSE(MATCH(B2&A2,{"IMPROBABLEACCEPTABLE","IMPROBABLETOLERABLE","IMPROBABLEUNDESIRABLE","IMPROBABLEINTOLERABLE","POSSIBLEACCEPTABLE","POSSIBLETOLERABLE","POSSIBLEUNDESIRABLE","POSSIBLEINTOLERABLE","PROBABLEACCEPTABLE","PROBABLETOLERABLE","PROBABLEUNDESIRABLE","PROBABLEINTOLERABLE"},0),"Low", "Medium", "Medium","High", "Low", "Medium", "High", "Extreme", "Medium", "High", "High", "Extreme")