Value based on dropdown selection (multiple entries separated by comma)

Hello,

I have two sheets in my excel named: "Risk Level" and "Jurisdiction Risk Register".

In sheet "Risk Level", cell E2, I have the option to select countries (the list of countries comes from "Jurisdiction Risk Register" sheet). There is a VBA code, which allows me to select multiple countries separated by comma.

My goal is to have a specific value in cell F2 (highlighted in blue) based on dropdown selection in cell E2. The logic is as follows:

Rule 1. If E2 contains any country that is classified "CRITICAL", then the value of F2 should be equal to "PROHIBITED"
Rule 2. IF E2 contains any country that is classified "VERY HIGH", then the value of F2 should be equal to "HIGH"
Rule 3. IF E2 contains any country that is classified "HIGH", then the value of F2 should be equal to "HIGH"
Rule 4. IF E2 contains any country that is classified "MEDIUM", then the value of F2 should be equal to "MEDIUM"
Rule 5. IF E2 contains country(ies) that are all classified "LOW", then the value of F2 should be equal to "LOW"

The country risk classification is based on column D in sheet named "Jurisdiction Risk Register"

The above five rules are in order of their priority meaning that Rule 1 overrides Rule 2 which overrides Rule 3 and so on. For example, if cell E2 is equal to "Russia, Armenia" then the value of F2 should be "CRITICAL" (Here Russia is classified as "CRITICAL" /Rule 1/, while Armenia is classified as "MEDIUM" /Rule 4/)

I know this might sound a little bit complicated but any help is much appreciated.

It seems when I select "Afghanistan, Bulgaria" it returns a value of "not found" in F2. However, it should have returned "PROHIBITED", as Afghanistan is "Critical" and Bulgaria is "Very High" (Rule 1 and 2, and Rule 1 should override Rule 2 )

Hello,

Thanks for your help. When I put this macros into vba this does not work (maybe I am doing something wrong). Could you please put this into the excel, so that I can download and test it? Thank you very much.

Hi @emilemil222 Response to POST # 5, the attachment has been prepared and placed in POST # 2

Thanks for the response, could you please state what is the difference between Run1 and Run 2 buttons

Because I should say these two, I wonder if you are suitable for either one in order to achieve your goal
Guess it's the first one

Hello,

Thank you very much this works perfectly. Is it possible to make one small adjustment? With all the rules still in force, whenever, country "Bulgaria" is in selection then the risk level is "High". If Bulgaria is not in the selection then still same logic applies as in my previous post. Thank you very much.

Sorry for the confusion. Indeed, you noted it correctly. When Russia (Critical) and Bulgaria (Very High) are in selection, the overall risk still should be prohibited. I have mad some changes to the rules (in bold). Can we please formulate formula with the rules below?

Rule 1. If E2 contains any country that is classified "CRITICAL", then the value of F2 should be equal to "PROHIBITED".

Rule 2. IF E2 contains "Bulgaria", then the value of F2 should be equal to "HIGH".

Rule 3. IF E2 contains any country that is classified "VERY HIGH", then the value of F2 should be equal to "PROHIBITED".

Rule 4. IF E2 contains any country that is classified "HIGH", then the value of F2 should be equal to "HIGH"

Rule 5. IF E2 contains any country that is classified "MEDIUM", then the value of F2 should be equal to "MEDIUM"

Rule 6. IF E2 contains country(ies) that are all classified "LOW", then the value of F2 should be equal to "LOW"

hi thanks for the response. This does not seem to work. Could you please upload the vba code into the excel (post #1), so that I can download and test it? Also, does it have rule 2 (on Bulgaria) incorporated?. Thank you.

based on this formula when I select Russia (Critical), and Bulgaria (Very High) it returns "HIGH", however it should return "Prohibited" (see post #17)

Hi @emilemil222 Response to POST # 20, the attachment has been prepared and placed in POST # 18