Formula to check if cell value in a range exists or does not exist in another range

Almir's picture

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.

Compare two ranges
AttachmentSize
Compare_Two_Ranges.xlsx10.65 KB