Although there is a more elegant way created by Nick at: http://www.excelexperts.com/Compare-2-Lists, here is one more way.

Let's suppose there are two ranges with some common values. In order to find what values from one range exist in another range and vice versa, use the following formula in the cell beside A2 cell in the first range:

=IF(COUNTIF($D$2:$D$10;A2)>0;"Exists in Range Two";"Nope")


Where A2 is a cell in the first range to be checked, and range D2:D10 is another range. Note that range is referenced absolutely. Just fill down the formula to the last row.

This formula will return "Exists in Range two" if the value from A2 from the first range exists anywhere in another range, no matter how many times.

To find values from Table 2 that exist in Table 1, copy formula to E column and change it to:


=IF(COUNTIF($A$2:$A$10;D2)>0;"Exists in Range One";"Nope")


Ranges do not have to be sorted.

Look at the example atached.