Excel: conditional formatting, check if column con

Question

First sheet:

First name  Last name       Role
Valeria     Bianchi         Admin
Evelise     Verdi           Teacher
Daniele     Rossi           Teacher
Annamaria   Neri            Admim
Anna        Gialli          Manager
Anna Bella  Proietti        Teacher
Simona      Ciaociao        Manager
Elisabetta  Lilla           Admin

Second sheet:

Email       Name
[e-mail]  Valeria Bianchi
[e-mail]  Daniele Rossi   
[e-mail]  Annamaria Neri  
[e-mail]  Anna Gialli 
[e-mail]  Simona Ciaociao
[e-mail]  Elisabetta Lilla

Now I have a rule for conditional formatting that colors the rows in the first sheet if Role is equal to Teacher: =$C2:$C200="Teacher". I'd like to add a second rule that colors the row of another color if Role is equal to Teacher AND in the second sheet is present its name in the Name column (only the row with Daniele Rossi should match this rule).

Hope it was clear.. :)

Answer 1

You will need to use the AND operator, to reference another sheet use the !. Example

=AND(C2="Hello", OtherSheet!C4="Hi")

Answer 2

This will do the trick. You will have to fix the cell references to match your sheet.

Also make sure you have the order of the rules correctly and stop processing more rules if there is a match.

The concatenate adds first and lastname columns in first sheet Sheet2!$a$1:$a$6 is the range for the names in the 2nd sheet

=AND(MATCH(CONCATENATE(A2;" ";B2);Sheet2!$A$1:$A$6;0); C2="Teacher")