Comparing Lists Not Working

[that_one_girl]

Hi again -

So I am trying to compare lists.

I have 3 workbooks that I am using.
#1 - Has Mandated Classes with staff names
#2 - Has training records for all staff (current and retired)
#3 - Is a master reference workbook

What I'm trying to do is
In #1 - create a formula that will look for staff name in A7 and ID# and find it in workbook #2
If that name is found, then find a class listed in #2 from a list of classes in #3
If #2 has an employees name, and a class next to it from the list in #3 , then in #1 show the name of the class that was taken. I only need one of the course names. It doesn't matter which. Just one of them.

EXAMPLE:
#1 - Mandated Classes
[TABLE="width: 402"]
<tbody>[TR]
[TD]NAME
(A)
[/TD]
[TD]CSHO/EE ID
(B)
[/TD]
[TD]UNIT
(C)
[/TD]
[TD]CLASS
(D)
[/TD]
[/TR]
</tbody>[/TABLE]
ACEE, J O6260 ENF <blank> <-------------I need this to show "Electrical Safety" or stay blank if there is no match.


#2 - Training Records ("ALL RECORDS" tab)
[TABLE="width: 402"]
<tbody>[TR]
[TD]CLASS
(C)
[/TD]
[TD]HRS
(D)
[/TD]
[TD]NAME
(E)
[/TD]
[TD]CSHO ID
(F)
[/TD]
[/TR]
</tbody>[/TABLE]
Electrical Safety 8 ACCE, J O6260



#3 - Master Reference Book ("MAND. TBL REFERENCES" tab)
[TABLE="width: 321"]
<tbody>[TR]
[TD]TECHNICAL CLASS 1
(A)
[/TD]
[/TR]
[TR]
[TD]Agricultural Safety and Health
[/TD]
[/TR]
[TR]
[TD]Applied Welding Principles
[/TD]
[/TR]
[TR]
[TD]Certified Safety Professional Exam Preparation
[/TD]
[/TR]
[TR]
[TD]Combustible Dust
[/TD]
[/TR]
[TR]
[TD]Concrete Forms and Shoring
[/TD]
[/TR]
[TR]
[TD]Construction Safety
[/TD]
[/TR]
[TR]
[TD]Cranes and Rigging
[/TD]
[/TR]
[TR]
[TD]
[TABLE="width: 321"]
<tbody>[TR]
[TD]Fall Arrest Systems
[/TD]
[/TR]
[TR]
[TD]Fire Protection
[/TD]
[/TR]
[TR]
[TD]Electrical Safety
[/TD]
[/TR]
[TR]
[TD]Heat Illness
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

This is the formula I have. Obviously.........it's not working. Any help? Pretty please.......

=IF(COUNTIFS('DOSHTrainingRecordsVer.03-AL.xlsm'!SUBJECT,'[MasterRefSheets.xlsm]MAND. TBL REFERENCES'!$A$2:$A$42,'DOSHTrainingRecordsVer.03-AL.xlsm'!CSHO_NAME,$A7,'DOSHTrainingRecordsVer.03-AL.xlsm'!CSHO_ID,$B7),CELL"contents","")</blank>