Conditional Formatting using VLOOKUP

[Knawl]

I want to monitor if a status has changed on Sheet1 between two columns - but only unexpected changes.

I have a list on Sheet2 with column A containing original status and column B containing a list of changes the status could expect to make.

I know a named range has to be used because sheet referencing doesn't work for conditional formatting.

So I've been using this in the conditional formatting:

=VLOOKUP(B2,change,2,0)=A2

"change" refers to the named range of the two columns described on Sheet2.

As you can see from the attached, 'Limp' has been correctly identified as unexpected, 'Leap' and 'Hike' are expected and should not be highlighted, and 'Fall' at the bottom should also be highlighted but is not.

vlookupCF.xlsx

[BadlySpelledBuoy]

It doesn't work because VLOOKUP stops looking when it finds the first instance of a match. So looking up "Walk" will only find "Walk" and not "Hike" nor "Stroll".

Also, your conditional formatting formula starts in row 2 but your data starts in row 1 so everything is offset.

[Knawl]

Ah okay. What do I need to do to make it account for every instance? Thanks.

[Jonmo1]

You could make 2 named ranges on sheet 2
1 for Column A - Call it Original
1 for Column B - Call it Change

Then use
=COUNTIFS(Original,A1,change,B1)

[BadlySpelledBuoy]

Is this how you'd expect it to look?

EDIT: Jonmo1 beat me to it!

[Jonmo1]

Although these solutions seem to have it backwards.
They highlight the ones that DO match, but I think OP wants to highlight the ones that DON'T match.

So instead of >0, use =0

=COUNTIFS(Original,A1,change,B1)=0

[Knawl]

That's great. Is there anyway to reverse the formatting? Only highlight when changes are detected instead of non-changes?

[BadlySpelledBuoy]

See post #6

[Knawl]

Excellent thanks. I used this to stop blank cells i the column from highlighting:

=IF(A1>0,COUNTIFS(orig,A1,change,B1)=0,"")