Highlighting match based on looking up 2 values in list

[SarahLouM]

Struggling to get my head around this one and think I might be overcomplicating when there's a simple answer in sight!
I have a training matrix showing dates of training for staff. Say something like this..

Name	Position	Training 1	Training 2	Training 3	Training 4	Training 5	Training 6	Training 7	Training 8	Training 9	Training 10	Training 11
Bob	Manager			17/03/2023
Jane	Supervisor		17/03/2023
Sue	Assistant
Joe	Assistant					17/03/2023

What I want to do, ideally, is have another tab where I've got a reference table showing the requirements for each job position, say something like this

Manager	Supervisor	Assistant
Training 1	Training 6	Training 1
Training 2	Training 7	Training 6
Training 3	Training 8
Training 4	Training 9
Training 5
Training 6
Training 9
Training 10
Training 11

And using this data, the initial table with my matrix of staff members would highlight where there was training required based on their job role:

Name	Position	Training 1	Training 2	Training 3	Training 4	Training 5	Training 6	Training 7	Training 8	Training 9	Training 10	Training 11
Bob	Manager			17/03/2023
Jane	Supervisor		17/03/2023
Sue	Assistant
Joe	Assistant					17/03/2023

I'm all in a scramble over how to get it to both look up to see which column it should be looking at, and if the match of training reference occurs or not in that column.
I may also be going at this in entirely the wrong way - any tips would be greatly appreciated!

Thanks

 

[SarahLouM]

Forgot to say - I'm using office 365

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name	Position	Training 1	Training 2	Training 3	Training 4	Training 5	Training 6	Training 7	Training 8	Training 9	Training 10	Training 11
2	Bob	Manager			17/03/2023
3	Jane	Supervisor		17/03/2023
4	Sue	Assistant
5	Joe	Assistant					17/03/2023
6
7
8
9
10
11	Manager	Supervisor	Assistant
12	Training 1	Training 6	Training 1
13	Training 2	Training 7	Training 6
14	Training 3	Training 8
15	Training 4	Training 9
16	Training 5
17	Training 6
18	Training 9
19	Training 10
20	Training 11
21
Master

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:M5	Expression	=AND($B2="Supervisor",COUNTIFS($B$12:$B$20,C$1))	text	NO
C2:M5	Expression	=AND($B2="Assistant",COUNTIFS($C$12:$C$20,C$1))	text	NO
C2:M5	Expression	=AND($B2="Manager",COUNTIFS($A$12:$A$20,C$1))	text	NO

 

[SarahLouM]

Thank you - I was definitely making it too complicated by trying to avoid doing separate formatting for every job role!
Simple option did work, just took a while to get in place for all roles
Thanks!

 

[Fluff]

Glad to help & thanks for the feedback.