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).

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 280"]
<tbody>[TR]
[TD="class: xl66, width: 280, align: right"]X ->[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD="width: 40"]small[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 38"]
<tbody>[TR]
[TD="width: 38"]large[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 280"]
<tbody>[TR]
[TD="class: xl66, width: 280, align: right"]Y ->[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD="width: 40"]tiny[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 38"]
<tbody>[TR]
[TD="width: 38"]giant[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="width: 280"]
<tbody>[TR]
[TD="class: xl65, width: 280"]this castle is small with large windows into which giants may peer[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD="width: 40"]small[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 38"]
<tbody>[TR]
[TD="width: 38"]large[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][TABLE="width: 40"]
<tbody>[TR]
[TD="width: 40"]tiny[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 38"]
<tbody>[TR]
[TD="width: 38"]giant[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 280"]
<tbody>[TR]
[TD="class: xl65, width: 280"]this castle is small with large windows into which giants may peer[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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,"")