IF and AND in the Same Function

[Guest]

I am searching for duplicate claims in adjoining rows and am using
=IF(a2<>a3,"no Match"," A representing the claim number. I need to satisfy
two IF statements though where S represents serial numbers in =IF(s2<>s3),"no
Match","
How do I use an AND or & to join the 2 conditions so they are both true to
get No Match or false to get a blank cell? Excel gives me
=IF((C1403<>C1404)*AND(S1403<>1303),"no match"," ") but still only the first
condition is being met to get " "

 

[Guest]

I just tested this and it works--copy and paste it in.

=IF(AND(C1403<>C1404,S1403<>S1303),"no match"," ")

You were missing the second S--the one that should have been right before
1303.

Regards,
Peter

 

[Guest]

By Jove, Pete, I think you've got it! Thank You!

 

[Guest]

I just retried this formula in my larger spread sheet and it did not work.
Only the second set of cells (S) have to match to get the " " result and not
both. The separating , is acting as an OR statement instead of an AND
statement.

 

[Guest]

This works--=IF(AND(C1403<>C1404,S1403<>S1404),"no match"," ").

Did you want to test S1304??? I had to change that last clause a second time.

 

[Guest]

Sorry if I typed the wrong cell #s. Here's where I am finding the problem
with your function. Using C1403: D26118160B S1403: N4C305113
C1404: D26118161A S1404: N4C305113
Because the "S" cells are the same I am getting a Match even though the 2
rows are not equal, only "S" part of the argument is true and I should be
getting No Match instead of a blank cell showing a match. (Theoretically
these can be cells a1,a2 and b1 and b2). The end result I need is that IF
C1403=C1404 AND IF S1403=S1404 then Match. If you are getting No Match then
my Excel may be corrupt.
Robert

 

[Guest]

Try pasting in the formula I sent on the last post. I had tested it and it
worked, after I corrected the missing S and the 1304 vs. 1404 type problem.
I am heading out for a poker game--wish me luck!

 

[Myrna Larson]

Hi, Pete:

He says "The end result I need is that IF C1403=C1404 AND IF S1403=S1404 then
Match."

Your formula returns NO MATCH if BOTH cells are different, i.e. if just one
matches but not the other your formula says it's OK. YOu should be using OR,
not AND.

One could also just convert the sentence above into a formula, i.e.

=IF(AND(C1403=C1404,S1403=S1404),"","no match")

 

[Guest]

Good point! I was correcting Robert's syntax but I was remiss in not
reviewing his original logic. I was also trying to help some folks the same
way you helped me today on the clock questions.

Rob I hope you've gotten where you need to be!

Best,
Peter

 

[Guest]

Myrna & Pete:
Thank you for all your help. Myrna, your formula worked. I did not realize I
could use and = sign. All the examples I saw including the Excel Help formula
all used <> and not =. This definately worked and thank you again. Pete, I
hope you came out a winner in your poker game!
Robert