# IF statement to compare multiple cells?

#### 247lanparty

##### New Member
I am trying to perform a function to lookup and compare a single cell at a time in one column to an entire seperate column on another worksheet to see if the values are reoccuring. I tried using an IF but couldn't get it to work.. any ideas how to do this?

Thanks

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

#### IML

##### MrExcel MVP
to get a count of how many times the value of cell A1 is in column H, you could use
=COUNTIF(H:H,A1)

to use get a true false answer if column h contains the cell A1 value at least once, use
=ISNUMBER(MATCH(H:H,A1,0))

in both cases, you would be better served from a performance standpoint by replacing H:H with cell references or a named range

##### Well-known Member
Not sure I fully have what you want, but perhaps this will get you started. Suppose list 1 is in Column A, and the other in Column E.

In B1 put this formula:

=IF(COUNTIF(E:E,A1)>0,3,1)

copy down to the bottom of column A.

Then in F1 put this formula:

=IF(COUNTIF(A:A,E1)>0,3,2)

and copy down to bottom of column E.

Now, those in column B with 1 are unique to Column A, those with 3, are in both Col. A and Col E. In Column F, if 2, then unique to Col E, if 3, then both in Col. A and Col. C.

Then use this formula as the tally, in G1:

=COUNTIF(B:B,1)+COUNTIF(F:F,2)

#### 247lanparty

##### New Member
Hmm... I think this would have worked but The problem is that I have to compare cells with text (numbers & letters)...

can I still do this cause it didn't work

#### just_jon

##### Legend
Can you an example of the formula you tried, what your answer was and what you expected? Post a small, representative sample of your data?

#### 247lanparty

##### New Member
Sheet1 column D has data such as "PRTN_PORT=PM1-0010-17" all through the column only each cell is a little bit different... Here is an example few cells in COlumn D:

PRTN_PORT=PM1-0010-17
PRTN_PORT=PM1-0021-21
PRTN_PORT=PM1-0007-17
PRTN_PORT=PME-0656

Now on sheet2, I have column E with the same sort of entries, but the data is from a different date and I need to reference which entries are reoccuring. Like, is PRTN_PORT=PME-0656 showing up on Sheet 2 and Sheet 1 or is it unique.

Thanks for the help.. i love this site!

#### just_jon

##### Legend
Book2
EFGH
1
2PRTN_PORT=PM1-0021-211
3PRTN_PORT=PM1-0010-160
4PRTN_PORT=PME-06561
5PRTN_PORT=PM1-0010-171
Sheet2

This sort of thing did not work for you? Is your data coming from 2 different applications? [BTW, anything other than 1 means Duplicated data.]

#### 247lanparty

##### New Member
ok.. got it now.. not sure why I couldn't get it to work first time... Now I did some conditional format to where anything duplicated (in my case with a 1) is hilighted yellow.. thanks!

#### just_jon

##### Legend
OK, and I MEANT to say anything but 0 means Duplicate data!

Replies
3
Views
183
Replies
7
Views
63
Replies
3
Views
232
Replies
1
Views
79
Replies
1
Views
98

1,186,156
Messages
5,956,254
Members
438,243
Latest member
FXA

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back