highlight matches with vlookup

I'm trying to highlight with conditional formatting the dates in column B that don't match by looking up names in column A in a different tab.

heres whats in sheet1:

column a column b
josh 2/10/2022
mark 2/11/2022
jane 2/15/2022
william 2/13/2022
mike 2/14/2022
sam 2/11/2022

this is sheets2:

column a column b
josh 2/10/2022
mark 2/11/2022
jane 2/12/2022
william 2/13/2022
mike 2/14/2022
sam 2/15/2022

Thanks!

This assumes you have headers, if you dont have a header then start with =VLOOKUP($A1,Sheet2!$A:$B,2,FALSE)=$B1 otherwise it would be starting with row 2

Please Login or Register  to view this content.

Any time you are attempting to figure out the logic of conditional formatting, work on the sheet. Figure out the formula that always comes to TRUE for any logic that you are attempting to utilize. Once you have it resulting in TRUE as desired then take that and apply it to a rule in conditional formatting over the range you wish it to evaluate.

This Conditional Formatting formula should help. Assumes data on both sheets are in A1:B_, so adjust the formula as needed. Select your date cells on Sheet1 first, of course.

Formula:

Please Login or Register  to view this content.

Its based on your applied to range, if you want only the dates to highlight then when you setup the rule - the "Applies to Range" should be just the date column. Formula stays the same... also my bad above I had it highlighting matching dates rather than the NON matching dates...

flip the = to the <> which is to say is not equal like Paul has done

You don't put "=$C:$C<>.....", just one cell reference, the first in the list.

So select C1:Dxx (e.g. C1:D100) then open the Conditional Formatting > New Rule > Use a formula... > type the formula =$C1<>VLOOKUP($C1,july20!$C:$D,2,0)
Then click Format and format as you wish and click OK a few times.