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.