comparison with simple 'if' formula

**BigPole** · 05-12-2010, 07:05 PM

I have to compare two sets of numbers to find out which are not present in the other.

Let's say I have one column with 4 cells: 1,2,3,4 and another column with 1,2,4. What I want to do is to have a simple formula that returns 'Yes' if the cell in the first column has an equivalent in any cell of the other column or 'No' if there's no such an equivalent found in any cell of the second column.

So if I use the data example above I would get:
1 Yes
2 Yes
3 No
4 Yes

I just have no idea how I can reference to a range of cells - I know how to compare one cell with another single cell - but I don't know how to compare one cell with any cell within a range of cells. Let's say from B2 to B100

I hope someone has an answer

**daddylonglegs** · 05-12-2010, 07:17 PM

You can use this formula in C1 to check whether B1 matches any cell in A1:A100

=IF(COUNTIF(A$1:A$100,B1),"Yes","No")

You can then copy that down the column to compare subsequent column B values against the same range