If X exists in $A3 and Y does not exist in $A3 then display Y

[ccjejohnson]

I'm trying to compose a formula for if X exists in $A4 and Y does not exist in $A4 then insert Y

Please see sample spreadsheet snippet below. The closest I've come is =IF(ISNUMBER(SEARCH(B$2,$A4)),B$3,"") which only checks if X exists then inserts Y. Having trouble with the "does not exist" part. Any ideas? I'm using Excel 2016 (not Office365).

1	A	B	C
2	X -> <tbody> </tbody>	small <tbody> </tbody>	large <tbody> </tbody>
3	Y -> <tbody> </tbody>	tiny <tbody> </tbody>	giant <tbody> </tbody>
4	this castle is small with large windows into which giants may peer <tbody> </tbody>

<tbody>
</tbody>

 

[ccjejohnson]

My snippet and sample formula in the original post is a bit off. Here's another version that's more aligned with the thread subject.

If B$1 exists in $A3 and B$2 does not exist in $A3 then display B$2 in B$3

	A	B	C
1		small <tbody> </tbody>	large <tbody> </tbody>
2		tiny <tbody> </tbody>	giant <tbody> </tbody>
3	this castle is small with large windows into which giants may peer <tbody> </tbody>

<tbody>
</tbody>

 

[JazzSP8]

Does this send you on the right track?

=IF(AND(ISNUMBER(FIND(B1,A3)),NOT(ISNUMBER(FIND(B2,A3)))),B2,"")

 

[ccjejohnson]

Thanks for the reply! I managed to get an answer on another forum that solved the equation (see below), but yours looks to work just as well. Thanks again!

=IF(AND(COUNTIF($A3,"*"&B$1&"*")>0,COUNTIF($A3,"*"&B$2&"*")=0),B$2,"")