How to set cell color based on several factors - help appreciated

Hi

I have tried for many hours before reaching out to this forum, please help me out.
I am trying to map skills for resources in the company, and I had made a small version of the sheet I am working on which is uploaded here.

I got the main function to work:
-E4 → choose your name
-E5 → Choose your position
The cells which need input for your main skills are colored white (using conditional formatting)

Now here comes the issue:
For a position there are expected secondary skills, for instance, an instrument engineer shall have knowledge of electrical engineer tasks and QHSE engineer tasks.
For a piping engineer the secondary skills would be structural and mechanical engineer skills (excluded from the attached sheet).
And so on.

When E5 = "Instrument Engineer" then Excel should look at the skill matrix for what the secondary skill(s) is for an instrument engineer. In this example this would be two secondary: QHSE engineer and electrical engineer.
(The same goes for tertiary skills)
Then excel shall color the cells representing secondary skills e.g. gray (any color really), and color the tertiary skills another color.
That way when a person clicks his own name and his own position (E4 & E5) in this large sheet with many names and many skills, it will be much easier to see where his input is needed.

This is useful for identifying which resources can be used for alternative work, vacation planning as to always have the right skill available during vacation times, see where more training is needed, evaluate who to promote, and show people what they should learn if they want to go one way or another.

Help is appreciated!

Hi Hans
Thank you, your solution worked just as I wanted it to. It was a clever solution to use countifs.
Happy new year