# Problem: Conditional formatting Match 2 Columns

#### Arnolf

##### Board Regular
Hello,
I have the next conditional formatting in the range A4:A16
=COUNTIF(B\$4:B\$16,A4)>0
(that identify any matches between columns A and B)

The problem I have, is that I need check one match by instance.
My problem with number 3.

I need the result of column D.

Or, if I have two instances of 3, I need the result of column G.

If I would have 3 instances of 3 or another number, the conditional formatting should identify the same instances of that number in the adjacent column.

rgds,
Arnolf
cformat.xls
ABCDEFGH
1
2
3
4919191
5828282
6131313
77773
8222
9333
10333
11333
12444
13555
14666
15
16
17
Sheet5

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Arnolf

I cannot understand exactly what you are after. No replies after 30 'hits' probably means others are having the same problem. Could you try to explain again in different words?

Peter,

I have 2 columns of data. Column A and Column B.
I want Conditional Formatting highlight all the numbers of column A that are identical/match in column B.
I did this with the formula: in range A4:A16 =COUNTIF(B\$4:B\$16,A4)>0

So, I've got this with conditional formatting: (problem with number 3)
cformat.xls
ABCD
1
2
3
491
582
613
77
82
93
103
113
124
135
146
15
16
17
Sheet5

But, I need this with conditional formatting:
cformat.xls
ABCD
1
2
3
491
582
613
77
82
93
103
113
124
135
146
15
16
17
Sheet5

Book8
ABCD
3XY
491
582
613
77
82
93
103
113
124
135
146
Sheet1

The non-numeric label in A3 is required.

The formula for the Formula Is option is:

=ISNA(MATCH(\$A4,\$A\$3:A3,0))*ISNUMBER(MATCH(\$A4,\$B\$4:\$B\$6,0))

Thank you for the solution. It certainly does what I was looking for.
But, here starts my second problem that is crashing my head:
If I enter another number 3 in column B, the conditional formatting formula doesnt highlight that second number 3 in column A.

I also need this:
cformat.xls
ABCD
1
2
3XY
491
582
613
773
82
93
103
113
124
135
146
15
16
17
Sheet5

And I need this (if I have 3 numbers 3 in column B)
cformat.xls
ABCD
1
2
3XY
491
582
613
773
823
93
103
113
124
135
146
15
16
17
Sheet5

Try...

Formula Is:

=(COUNTIF(\$A\$4:A4,A4)<=COUNTIF(\$B\$4:\$B\$8,A4))*ISNUMBER(MATCH(\$A4,\$B\$4:\$B\$8,0))

Hope this helps!

Thank you Domenic!!!
That is it.
It is exactly what I am looking for.

Cheers,
Arnolf

Would the shorter
=(COUNTIF(\$A\$4:A4,A4)<=COUNTIF(\$B\$4:\$B\$8,A4))*ISNUMBER(A4)
do the same job?

Peter_SSs said:
Would the shorter
=(COUNTIF(\$A\$4:A4,A4)<=COUNTIF(\$B\$4:\$B\$8,A4))*ISNUMBER(A4)
do the same job?

Yep, definitely! In fact, so would...

=(A4<>"")*(COUNTIF(\$A\$4:A4,A4)<=COUNTIF(\$B\$4:\$B\$8,A4))

Replies
12
Views
436
Replies
3
Views
312
Replies
7
Views
168
Replies
3
Views
251
Replies
3
Views
282

1,196,264
Messages
6,014,320
Members
441,814
Latest member
youngstubbs

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back