Trying to highlight missing training from completed training

Welcome to the forum

I shortened your tab names just to keep the formula length manageable during development - feel free to change them back.

Department names have to match on all sheets! On the "Mandatory" sheet I changed "Dept XYZ" to "XYZ" etc. Also in E3 (at least) on the "Mandatory" worksheet there is a spurious "space" character after "Yes" that would cause the formula to fail.

The full set of courses needs to exist on all 3 worksheets.

All of the "Required table" worksheet is calculated - there is no direct data entry to this sheet.
I added a few extra employees for the sake of testing. Here's how the "Required Table" worksheet looks:

courses.png

A2 copied down: =Completed!A2
B2 copied down: =IFERROR(VLOOKUP(A2, Completed!A:B,2, FALSE), "")
C2 copied across and down to G7:

Formula:

=IFERROR(IF(ISNUMBER(INDEX(Completed!$C$2:$G$10, MATCH($A2, Completed!$A$2:$A$10, 0), MATCH(C$1, Completed!$C$1:$G$1, 0))),

     INDEX(Completed!$C$2:$G$10, MATCH($A2, Completed!$A$2:$A$10, 0), MATCH(C$1, Completed!$C$1:$G$1, 0)),

   IF(INDEX(Manadatory!$B$2:$F$3, MATCH($B2, Manadatory!$A$2:$A$3, 0), MATCH(C$1, Manadatory!$B$1:$F$1, 0))="Yes", "Missing", "n/a")), "")

The attached update to your workbook implements the above.

Let us know if this is what you are looking for.

Glad I could help. Thanks for the positive feedback and the rep.