another conditional format question - highlight if date AND names match a condition?

hello - new here, and self taught excel tinkerer
i've figured out few problems by copy/paste and modifying what others have done but can't seem to figure this one out.

i have two sheets

sheet 1 - rows of dates and columns of names for shifts
sheet 2 - rows of dates and columns of names and shifts called "off" - which is essentially a vacation sign off list i download

so i'm trying to make the cell highlight if there is a match on a particular date - it would indicate both assigned and off, which would be a problem!

i was thinking countifs - match the date, and then match the names, >0 would color... otherwise ignore... can't seem to express that into excel though

and then i figured index the dates, match dates, and match names... not too fluent there either

=INDEX(B2:B100,MATCH(offs!C3:C100,offs!B3:B100))

doesn't work, and i tried countifs

=COUNTIFS(offs!$C$2:$C$100,B2,offs!$B$2:$B$100,E2)
but i need it to apply to the whole worksheet


actually, i got it to work!

just had to write it out i guess

Welcome to the Forum soondubu!

The appropriate formatting rule is

=COUNTIFS(offs!$B$2:$B$200,C2,offs!$C$2:$C$200,$B2)>0

It looks like you had the right idea but your COUNTIFS syntax is wrong. Not sure what you were trying to do there.

You updated your post just as I was posting. You now have correct syntax but it is still not the correct formula. I don't know what you did in your actual file to get it to work since you did not reattach it, but that formula in your revised post has a couple of problems:

=COUNTIFS(offs!$C$2:$C$100,B2,offs!$B$2:$B$100,E2)

offs column C is dates. B2 is a name. You don't want to compare a name to dates. This will always give a 0 result.

E2 is three columns to the right after the name you are actually checking.