CODEWITHSUNDEEP
excelformattingconditional-statements
SamC
SamC

Reputation: 47

Excel: Conditional Formatting based on ranges

Is there a way to highlight duplicate values if a range contains 2 or more values from a separate range?

If A1:A5 contains any 2 or more of the same values from B1:B5

I've tried a number of different methods, all of which I'm getting wrong.

Should it be something akin to...

if(a1:a5,b1,a1:a5,b2,a1:a5,b3,a1:a5,b4,a1:a5,b5)

...or a nested AND function?

I'm not sure I'm wording it correctly.

Thanx for looking, Sam

Upvotes: 0

Views: 85

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

use SUMPRODUCT(COUNTIF()), but you will need two rules one for A and another for B:

A:

=AND(SUMPRODUCT(COUNTIF($A$1:$A$5,$B$1:$B$5))>=2,ISNUMBER(MATCH($A1,$B:$B,0)))

B:

=AND(SUMPRODUCT(COUNTIF($A$1:$A$5,$B$1:$B$5))>=2,ISNUMBER(MATCH($A1,$B:$B,0)))

enter image description here

Upvotes: 2

Related Questions