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
1				ContainsL	ContainsR	Match
2	abcdefghijk	xxxabcdeyyfghijkyyyzz		1	1	Yes
3	xxxxxyy	aaxxxxxzz		1	0	Yes
4	aabbbb	xxabbbbzz		0	1	Yes
5	abcde	somethingelse		0	0	No
6				0	0	No

<tbody>
</tbody>

Sheet7​

Worksheet Formulas Cell Formula E2 =COUNTIF(C2,"*"&LEFT(B2,5)&"*") F2 =COUNTIF(C2,"*"&RIGHT(B2,5)&"*") G2 =IF(E2+F2>0,"Yes","No") <tbody> </tbody>

<tbody>
</tbody>

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
1				ContainsL	ContainsR	Match
2	abcdefghijk	xxxabcdeyyfghijkyyyzz		1	1	Yes
3	xxxxxyy	aaxxxxxzz		1		Yes
4	aabbbb	xxabbbbzz			1	Yes
5	abcde	somethingelse				No
6						No

<tbody>
</tbody>

Sheet7​

Worksheet Formulas Cell Formula E2 =COUNTIF(C2,"*"&LEFT(B2,5)&"*") F2 =COUNTIF(C2,"*"&RIGHT(B2,5)&"*") G2 =IF(E2+F2>0,"Yes","No") <tbody> </tbody>

<tbody>
</tbody>

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!