Stuck on simple formula.

[PACULA]

Could you some help, a co-worker and I have been stuck forever on this.

When the Location is the Jungle and the Country US AND the description is EITHER Tiger or Lion, return Animal. Otherwise, return column B (not found).

In the green section I can get Animal but the formula always returns FALSE if any of the conditions isn't true instead of not found.

Formula is below...what am I doing wrong?

=IF(AND(A2="Jungle",LEFT(D2,2)="US"),IF(ISNUMBER(SEARCH("Lion",N2)),"Animal",IF(AND(A2="Jungle",LEFT(D2,2)="US"),IF(ISNUMBER(SEARCH("Tiger",N2)),"Animal",IF(AND(A2="Jungle",LEFT(D2,2)="US"),B2)))))

 

[Dave87]

this seems to work

=IF(AND(OR(N2="Tiger",N2="Lion"),A2="Jungle",D2="US"),"Animal",B2)

 

[PACULA]

Thanks for responding. My example was too simple.

Dog is only a part of the description below.

That seems to be the issue and I'm not sure how to address with the formula...note that the green highlight should be either Dog or Cat, but I can't get one formula that does both; I always end up with a "False" instead of Cat.

The rule I'm trying to do is if DogUS is found in the Type and the word Dog is in the description, return Dog. Otherwise, return Rollup (Cat in the example).

=IF(AND(A3="Dog",LEFT(D3,2)="US"),IF(ISNUMBER(SEARCH("Dog",E3)),"Dog",B3))

The actual formulas are much longer, but if I can just get this building block right I can construct from there.

 

[Dave87]

if A2 is blank the answer will be Cat?

=IF(ISBLANK(A3),B3,IF(AND(A3="Dog",LEFT(D3,2)="US"),IF(ISNUMBER(SEARCH("Dog",E3)),"Dog",B3)))

if the above doesn't work can you please provide a bigger data sample

 

[ebea]

Thanks for responding. My example was too simple.

Dog is only a part of the description below.

That seems to be the issue and I'm not sure how to address with the formula...note that the green highlight should be either Dog or Cat, but I can't get one formula that does both; I always end up with a "False" instead of Cat.

View attachment 20174

The rule I'm trying to do is if DogUS is found in the Type and the word Dog is in the description, return Dog. Otherwise, return Rollup (Cat in the example).

=IF(AND(A3="Dog",LEFT(D3,2)="US"),IF(ISNUMBER(SEARCH("Dog",E3)),"Dog",B3))

The actual formulas are much longer, but if I can just get this building block right I can construct from there.

You'r missing a little part in the Formula (Bold in end of Formula): =IF(AND(A3="Dog",LEFT(D3,2)="US"),IF(ISNUMBER(SEARCH("Dog",E3)),"Dog",B3);B3)

 

[PACULA]

Thanks David. The formula should return the Rollup when any value (blank or otherwise) doesn't say DogUS (combination of Type and Group) and have Dog in the Description.

Thanks Ebea. I tried your suggestion but no luck...didn't work with the ;B3 at the end...Excel changed it to :B3 and still returned false.

Simplified view below. I really just need to figure out the formula for row 3...if I can get it to return either Dog or Cat every time...never False...I should be set. Any thoughts?

 

[ebea]

Thanks David. The formula should return the Rollup when any value (blank or otherwise) doesn't say DogUS (combination of Type and Group) and have Dog in the Description.

Thanks Ebea. I tried your suggestion but no luck...didn't work with the ;B3 at the end...Excel changed it to :B3 and still returned false.

Simplified view below. I really just need to figure out the formula for row 3...if I can get it to return either Dog or Cat every time...never False...I should be set. Any thoughts?


View attachment 20175

If you failed with this corrected Formula, then you'r example in #3, are not what you in reality have. I made the test out of #3, and it works. Test it with Excels Evaluate tools, in section Formula.
I can't see, how Excel should change the ; to a :

 

[Dave87]

@ebea has the answer but i think but it looks like a typo gets in there somewhere... and when you've corrected it in the picture in post #6 its not got the final B3 needed

=IF(AND(A3="Dog",LEFT(D3,2)="US"),IF(ISNUMBER(SEARCH("Dog",E3)),"Dog",B3),B3)

 

[Dave87]

If you failed with this corrected Formula, then you'r example in #3, are not what you in reality have. I made the test out of #3, and it works. Test it with Excels Evaluate tools, in section Formula.
I can't see, how Excel should change the ; to a :

change the ; or : to a ,

 

[PACULA]

Ebea, I'm new to this board (as a poster, anyways) and hope that I'm ok in posting a link to my file on Google Drive. If I'm supposed to follow another process, happy to.

If you try to input the formula suggested, you'll see the error message/suggested correction. If the correction is made, it's still false. If the correction isn't made, the formula fails.

MREXCEL file