finding matches in different columns

Posted by adam brucerd on August 02, 2000 11:13 AM

I don't know excel at all, and I'm sure there's an easy way to do this, but I can't figure out how.

I have two columns. One has a short list of email address, the other has a longer one. How can I get excel to highlight all the emails that are in BOTH of the columns?

any help would be great.

Thanks,
Adam

Posted by Celia on August 02, 0100 5:10 PM

Adam

I have assumed that the addresses are in columns A & B and that the first entries are in cells A1 & B1. You will have to amend the formulas below to fit your actual columns and starting cells.

Select column A
Go to Format/Conditional Formatting...
In the first box of Condition1 select Formula Is
Type the following formula in the second box :-
=IF(VLOOKUP(A1,$B$1:$B$10000,1,FALSE)=A1,TRUE,FALSE)
Select Format... and set the format you want to highlight duplicates.

Repeat the above steps for column B. The formula re column B :-
=IF(VLOOKUP(B1,$A$1:$A$10000,1,FALSE)=A1,TRUE,FALSE)

Celia

Posted by Celia on August 02, 0100 5:13 PM

Correction

Correction :-
Formula re column B should be :-
=IF(VLOOKUP(B1,$A$1:$A$10000,1,FALSE)=B1,TRUE,FALSE)

Celia