I have 3 columns with numbers in them. I want to compare all 3 against each other and see if any of them are not exactly the same. If possible put in another column a results that says which column doesn't match. I use exact but it's only for 2 columns. I also made 3 new columns that compares A1, A2 2nd column is A1,A2 and 3rd column compares A2,A3. But there in 3 separate columns. I was thinking of just having results of the comparison into 1 cell. Not sure if it could do it.

What do you mean by seeing if "any of them are not exactly the same"?Exactly the same (in my mind) would be that A1 = B1 = C1

andB2 = B2 = C2andA3 = B3 = C3, etc.Put this in D1 and drag it down. If you see

any0's (zeros) then the columns are notexactlythe same.=(A1=B1)*(A1=C1)*(B1=C1)

Hi, Taking DerbyDad03's solution, you can 'pretty it up' by wrapping it in an IF() statement:

=IF((A1=B1)*(A1=C1)*(B1=C1)=0,"Not fully matched","All match")

or this:

=IF(OR(A1="",B1="",C1=""),"Missing number",IF((A1=B1)*(A1=C1)*(B1=C1)=0,"Not fully matched","All match"))

which also tests for a cell with no data entered.Regards

Here is another way.

Put your Data in Columns A - D - GWe are going to use

Columns B & C to check Column D & G

Columns E & F to check Column A & G

Columns H & I to check Columns A & D

In Column B enter the formula:

=IF(ISERROR(MATCH(A2,$D$2:$D$13,0)),"No Match in D","")

Were matching column A to DIn Column C enter the formula:

=IF(ISERROR(MATCH(A2,$G$2:$G$13,0)),"No Match in G","")

Were matching column A to GIn Column E enter the formula:

=IF(ISERROR(MATCH(D2,$A$2:$A$13,0)),"No Match in A","")

Were matching column D to AIn Column F enter the formula:

=IF(ISERROR(MATCH(D2,$G$2:$G$13,0)),"No Match in G","")

Were matching column D to GI'll leave the last two columns H & I for you to do.

I tried posting an example, but the pre tags would not work.

MIKE

Thank you everyone. All of those work great!

Ask Your Question

Weekly Poll