Possible to match one group of cells with another group, but only in one direction?

[fisjon09]

I have a group of random, possibly repeating numbers in column A and another group of non-repeating numbers in column B. I ultimately want to highlight the numbers in column A that are the same as column B's numbers.

I could do this with the default highlight conditional formatting rule, but doing this would highlight numbers in A even if they don't exist in B. Are you following?

Take this as an example:

A	B
5	1
8	2
8	3
12	4
2	5
0	6
4	7
18	8
1	9
18	10

<tbody>
</tbody>

I need a way to highlight the 5, 8, 2, 0, 4, and 1 in column A by using column B's values, but without highlighting numbers that don't exist in B, like 0, 12, and 18, even though 18 is repeating and would show up in a standard duplicate value rule.

 

[StephenCrump]

Welcome to the Forum!

You could conditionally format A1:A10 using the formula: =COUNTIF(B$1:B$10,A1)

Excel 2010

	A	B
1	5	1
2	8	2
3	8	3
4	12	4
5	2	5
6	0	6
7	4	7
8	18	8
9	1	9
10	18	10

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

1
​

 

[fisjon09]

Welcome to the Forum!

You could conditionally format A1:A10 using the formula: =COUNTIF(B$1:B$10,A1)

Excel 2010

	A	B
1	5	1
2	8	2
3	8	3
4	12	4
5	2	5
6	0	6
7	4	7
8	18	8
9	1	9
10	18	10

<tbody>
</tbody>

1
​

Thanks! I wish I'd found this place sooner.

So maybe I didn't explain enough of what I'm actually after.

My actual spreadsheet has column A through G filled with random, sometimes repeating data. I need to highlight any cell that is the same as the numbers in column H. So it's just like this example I gave, except that there are a few more columns of data in addition to the A column here.

Does that make sense?

Thanks for the answer, by the way. I've used countif many times before and was excited when I saw it in your response, but now I'm not sure if I can use it for what I need.

 

[StephenCrump]

Do you mean like this?

A1:G10 conditionally formatted using formula: =COUNTIF($H$1:$H$5,A1)

Excel 2010

	A	B	C	D	E	F	G	H
1	4	6	5	8	6	10	2	2
2	4	3	3	5	4	2	4	4
3	5	5	5	10	6	10	5	6
4	8	1	9	7	2	8	10	8
5	2	7	2	3	2	6	6	10
6	2	6	9	2	4	3	5
7	1	5	4	2	2	9	1
8	8	4	4	4	3	7	10
9	4	9	5	9	3	5	3
10	7	5	4	5	5	1	5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

1
​

 

[fisjon09]

Do you mean like this?

A1:G10 conditionally formatted using formula: =COUNTIF($H$1:$H$5,A1)

Excel 2010

	A	B	C	D	E	F	G	H
1	4	6	5	8	6	10	2	2
2	4	3	3	5	4	2	4	4
3	5	5	5	10	6	10	5	6
4	8	1	9	7	2	8	10	8
5	2	7	2	3	2	6	6	10
6	2	6	9	2	4	3	5
7	1	5	4	2	2	9	1
8	8	4	4	4	3	7	10
9	4	9	5	9	3	5	3
10	7	5	4	5	5	1	5

<tbody>
</tbody>

1
​

Genius! Thanks so much!!