MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Compare columns


Posted by David Goeller on December 04, 2001 12:31 PM

I have combined two spreadsheets into one, I have one common column on both spreadsheets. I would like to compare the information in these two columns and return a found or not found answer. Example column a1:a3 has abc,abd,abe,,column b1:b3 has aba,abw,abc. abc in a1 was found in b3, so a found should be returned. How do I go about this?

Thanks,

Dave Goeller


Posted by Hansoh on December 04, 2001 12:36 PM

use =countif()...here it is...

column 1
abc
abd
abe
abf
abg
abh

column 2
aba
abw
abc
abg
aaa
abc

formula in cell D1. drag it down to as many cells you have in column1. should work.
=COUNTIF($B$1:$B$6,A1)


han

Posted by Aladin Akyurek on December 04, 2001 12:38 PM

=IF(COUNTIF($B$1:$B$3,A1),"Found","")

Copy down this as far as needed.

Note that the formula reterns "Found" if A-value is in B1:B3.

Aladin