comparing 2 columns to find matches

[zircom]

I need to look at 2 columns:

'A' 'B' 'C'

2345 2345 Y
4567 4568 Y
4568 6789 N
7890 7890 Y

Etc (3,500 numbers) and i need to see which ones in column 'B' are in column 'A'.

THanks

 

[PeterMoran]

Hi,

In cell C2 place the following formula:

=IF(A2=B2,"Y","N")

or

=IF(A2=B2,"","N")

(to just show the non-matching entries)

press Enter and check the result. Then copy down as far as needed.

Good Luck!

Peter Moran

 

[xlbo]

can they be out of sync ? ie in different rows ?

If so you can use

=if(countif($A$2:$A$3500,B2)>0,"Y","N")

and copy down

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[yogia]

Hi zircom:

Or ...

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[honorbum]

You can also use the "Match" operator.

=Match(A2, B:B, 0) (copy the formula the for each row)

MATCH(lookup_value,lookup_array,match_type)

Matchtype

It will return the line (or row) number of the record that matches A2 and #N/A if there is not a match for that row.

 

[zircom]

great response and they worked, i used a couple of them in the spread sheet to show a sanity check process.

thanks again