IF cell contains / does not contain

[surkdidat]

Apologies

My brain is not working this Monday morning!

Can someone please help and translate the following into a formula

IF B1 CONTAINS "cat" AND c14= "CR" then display "NA",

IF B1 CONTAINS "dog" AND c9 DOES NOT CONTAIN "dog", then display "NA"

Otherwise display "1"

Many thanks

 

[jproffer]

=IF(AND(B1="cat",C14="CR"),"NA",IF(AND(B1="dog",C9<>"dog"),"NA",1))

I don't think I missed any closing ")"...but maybe. It will tell you if I did

 

[surkdidat]

Hi

Thank you for this. However, on the formula I need "CAT" or "DOG" to be contained anywhere in cell, so the cell may contain more info, ie "CAT 2" or "DOG 3" (for example)....

=IF(AND(B1="cat",C14="CR"),"NA",IF(AND(B1="dog",C9<>"dog"),"NA",1))

I don't think I missed any closing ")"...but maybe. It will tell you if I did

 

[JazzSP8]

Just a small change to the Formula by jproffer;

=IF(AND(B1="*cat*",C14="CR"),"NA",IF(AND(B1="*dog*",C9<>"dog"),"NA",1))

HTH

 

[surkdidat]

Hi there, this does not seem to work. In cell B1 I have entered "cats" and in cell C14 I have entered "CR". I want this to return "NA" but currently is returning a "1" value.

Also when I change this for DOGS and in c9 enter "horse" for example, its still displaying a "1" not "NA"

Just a small change to the Formula by jproffer;

=IF(AND(B1="*cat*",C14="CR"),"NA",IF(AND(B1="*dog*",C9<>"dog"),"NA",1))

HTH

 

[snjpverma]

You need to use search function for this to work. The "And" function doesn't support wild characters.

 

[Peter_SSs]

Try

=IF(OR(AND(COUNTIF(B1,"*cat*"),C14="CR"),AND(COUNTIF(B1,"*dog*"),COUNTIF(C9,"*dog*")=0)),"N/A",1)

 

[snjpverma]

Peter_SS has already given a nice solution. But as I had mentioned earlier that it can be done via Search function too.
This is how the Search works

=IF(OR(AND(ISNUMBER(SEARCH("cat",B1)),C14="CR"),AND(ISNUMBER(SEARCH("dog",B1)),ISERR(SEARCH("dog",C9)))),"NA",1)

 

[surkdidat]

Thank you for this, seems to work nicely!!

However, one part I forgot, is that I also need:

B1 Cat Food
B2 Cat Tray
B3 Cat and mouse
.....
C13 Food
...........
If B1 CONTAINS Cat, and C13 is not in B1, I need that also to say "NA"

So for B1 it would pass the criteria and go to the end and put a "1", but if tray or "and mouse" are in C13, I want it to say "NA"

Apologies for not including it first time around, I didn't realise this would trip my sheet up until I entered the code

Peter_SS has already given a nice solution. But as I had mentioned earlier that it can be done via Search function too.
This is how the Search works

=IF(OR(AND(ISNUMBER(SEARCH("cat",B1)),C14="CR"),AND(ISNUMBER(SEARCH("dog",B1)),ISERR(SEARCH("dog",C9)))),"NA",1)

 

[surkdidat]

Following on from this I have the following formula :-

.........  AND(ISNUMBER(SEARCH($I$13,B1)),ISERR(SEARCH(B1,$I$13)))), ......

However, its doing the opposite of what I need. Its looking in I13, and putting "NA" against the one I want a 1 on, and a 1 on against the data I want " NA" on - how do I reverse the formula - (Its not as simple as changing the NA and 1 at the end of the formula round as this would affect other formulas

Thank you for this, seems to work nicely!!

However, one part I forgot, is that I also need:

B1 Cat Food
B2 Cat Tray
B3 Cat and mouse
.....
C13 Food
...........
If B1 CONTAINS Cat, and C13 is not in B1, I need that also to say "NA"

So for B1 it would pass the criteria and go to the end and put a "1", but if tray or "and mouse" are in C13, I want it to say "NA"

Apologies for not including it first time around, I didn't realise this would trip my sheet up until I entered the code