Conditional formatting relying on a number of other factors

[NicoleJones]

Hi

I have been wracking my brain trying to figure this one out.

Please see the sample attached to see what I am talking about, it is hard to explain.

Using Conditional Formatting, I am trying to get a name to highlight if that person has not completed an induction for a project.

The name I want to highlight is in Column G under 'Team 1'

The 'induction' is defined by the first 3 digits of the Project Number in 'area 2' (L6)

So I need the cell containing the name in Column G to refer to the table in area 1 to see if the name has conducted an induction (identified as a tick if they have completed it)

So in the sample 'name 1' should return a red cell value and 'name 2' should remain clear.

I really hope someone can understand this and help

Thank you in advance.

Nicole

[FDibbins]

I sure there are plenty of other ways to do this, but here is 1 suggestion for your CF rule....
=VLOOKUP(K9,C9:D15,MATCH(LEFT($L$6,3),$C$8:$G$8,0),0)=""