comparing two columns

[JoeM]

I have a number of ids in two columns and am trying to represent which ones
in the second column also appear in the first.

 

[T. Valko]

One way...

Comparing column B to column A with the list in column A in the range
A1:A20.

=IF(COUNTIF(A$1:A$20,B1),"match","")

Copy down as needed.

 

[Jacob Skaria]

Apply the formula in C1 which will check the value of B1 in A and return Y or N

=IF(COUNTIF(A:A,B1)>0,"Y","N")

 

[Glenn]

I have a number of ids in two columns and am trying to represent which ones
in the second column also appear in the first.

Assuming your two columns are A and B, select column B and enter this for
conditional formatting:

=COUNTIF($A:$A,B1)>0

Select the format you want to "represent" matches.

 

[Harlan Grove]

One way...

And the slow way . . .

Comparing column B to column A with the list in column A in the range
A1:A20.

=IF(COUNTIF(A$1:A$20,B1),"match","")

Bit faster to use

=IF(COUNT(MATCH(B1,A$1:A$20,0)),"match","")

or just

=MATCH(B1,A$1:A$20,0)

which would return a number when there's a match and #N/A when there
isn't.

 

[T. Valko]

I'm bored too!

--
Biff
Microsoft Excel MVP

And the slow way . . .


Bit faster to use

=IF(COUNT(MATCH(B1,A$1:A$20,0)),"match","")

or just

=MATCH(B1,A$1:A$20,0)

which would return a number when there's a match and #N/A when there
isn't.