IF statement on two MATCH results

[kyleno]

I have three columns:

E: ContainsL
F: ContainsR
G: Match

ContainsL:
=MATCH("*"&LEFT(B2,5)&"*",C2,0)

ContainsR:
=MATCH("*"&RIGHT(B2,5)&"*",C2,0)

Match:
=IF(E2+F2=2,"Yes", IF(E2+F2=1,"Yes","No"))
-----------------------------------------------------
ContainsL and ContainsR returns either a "1" or "#N/A"

I would like the Match column to read Yes IF the sum of ContainsL and ContainsR equals EITHER 1 or 2, No if it does not, but the #N/A value complicates this.


To get around the #N/A issue I know that I should be using the IF(ISNA(F2),"Yes","No")) (or something similar) formula but I'm not sure how to nest it properly.


Please help.

Thanks.

 

[Eric W]

You could use COUNTIF with wildcards to avoid the #N/A:

	B	C	D	E	F	G
ContainsL	ContainsR	Match
abcdefghijk	xxxabcdeyyfghijkyyyzz	Yes
xxxxxyy	aaxxxxxzz	Yes
aabbbb	xxabbbbzz	Yes
abcde	somethingelse	No
No

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

</tbody>

Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E2[/TH]
[TD="align: left"]=COUNTIF(C2,"*"&LEFT(B2,5)&"*")[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]=COUNTIF(C2,"*"&RIGHT(B2,5)&"*")[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G2[/TH]
[TD="align: left"]=IF(E2+F2>0,"Yes","No")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


If you want to stick with the MATCH formulas, try this in G2:

=IF(COUNT(E2:F2),"Yes","No")

 

[kyleno]

You could use COUNTIF with wildcards to avoid the #N/A:

	B	C	D	E	F	G
ContainsL	ContainsR	Match
abcdefghijk	xxxabcdeyyfghijkyyyzz	Yes
xxxxxyy	aaxxxxxzz	Yes
aabbbb	xxabbbbzz	Yes
abcde	somethingelse	No
No

<tbody>
[TD="align: center"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2
[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]

[TD="align: center"]3
[/TD]

[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]

[TD="align: center"]5
[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E2
[/TH]
[TD="align: left"]=COUNTIF(C2,"*"&LEFT(B2,5)&"*")
[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]F2
[/TH]
[TD="align: left"]=COUNTIF(C2,"*"&RIGHT(B2,5)&"*")
[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G2
[/TH]
[TD="align: left"]=IF(E2+F2>0,"Yes","No")
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


If you want to stick with the MATCH formulas, try this in G2:

=IF(COUNT(E2:F2),"Yes","No")

That worked! Thanks Eric!

 

[Eric W]

Glad to help!