IF AND Formula with multiple conditions

[thomassharp]

Hi,I am trying to use IF formula to show whether a row of cells matches:

Dog	Dog	Dog	Fish	Near Match
Dog	Dog	Fish	Fish	Partial Match
Cat	Pig	Fish	Dog	No Match
Pig	Pig	Pig	Pig	Full Match

<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>

I have tried =IF(A2=B2,"Partial Match","No Match")IF(AND(A2=B2,A2=C2),"Near Match","No Match"))
But obviously this doesn't work.

Very interested in the logic of the syntax needed here...

Thanks!

 

[admiral100]

Hi,

For the first row ( A2 to D2)

Ctrl+Shift+Enter Not just Enter

=CHOOSE(COUNT(FIND(A2,A2:D2)),"No Match","Partial Match","Near Match","Full Match")

 

[thomassharp]

I thank you. How does the formula know if the conditions have been met? Is it counting how many are cells are equal in the range and then needs an ascending list at the end ? So if we put full match in the middle it wouldn't work?

 

[admiral100]

if count = 1 then "No Match" 2 then "Partial Match" etc...

 

[Webo11]

Hello, I would do:

=+IF(AND(A1=B1,A1=C1,A1=D1),"Full Match",IF(OR(AND(A1=B1,A1=C1),AND(D1=B1,A1=D1),AND(C1=B1,B1=D1),AND(C1=A1,A1=D1)),"Near Match",IF(OR(A1=B1,A1=C1,A1=D1,B1=C1,B1=D1,C1=D1),"Partial Match","No Match")))

Hope it helps, is a long formula but it works ok.

Regards,

 

[Ruddles]

You could count the matches and get a value from 0 to 6:-

=(A1=B1)+(A1=C1)+(A1=D1)+(B1=C1)+(B1=D1)+(C1=D1)

Or you could select from a number of text values like this:-

=CHOOSE((A1=B1)+(A1=C1)+(A1=D1)+(B1=C1)+(B1=D1)+(C1=D1)+1,"None","One","Two","Three","Four","Five","Six")

Obviously instead of One, Two, Three, etc, you'd put No matches at all, One match, A couple of matches, Ooh three matches, Four not possible, Five not possible, Great - six matches!

Any use?

R.

 

[Webo11]

Hi,

For the first row ( A2 to D2)

Ctrl+Shift+Enter Not just Enter

=CHOOSE(COUNT(FIND(A2,A2:D2)),"No Match","Partial Match","Near Match","Full Match")

Work for me Thomas, you should do it this way!

 

[admiral100]

another way to avoid choice of a particular word

Ctrl+Shift+Enter Not just Enter
=CHOOSE(MAX(COUNTIF(A2:D2,A2:D2)),"No Match","Partial Match","Near Match","Full Match")

 

[thomassharp]

Hello, I would do:

=+IF(AND(A1=B1,A1=C1,A1=D1),"Full Match",IF(OR(AND(A1=B1,A1=C1),AND(D1=B1,A1=D1),AND(C1=B1,B1=D1),AND(C1=A1,A1=D1)),"Near Match",IF(OR(A1=B1,A1=C1,A1=D1,B1=C1,B1=D1,C1=D1),"Partial Match","No Match")))

Hope it helps, is a long formula but it works ok.

Regards,

Thanks. That is what I was trying but couldn't get there

 

[thomassharp]

Very helpful Ruddles, why do you need the plus 1 after the conditions?