IF AND OR Function

[olympiac]

I need a formula to give the following result

If Cell A2 is RAT
and cell B2 is "RATART" or "RAT" or "ARTRAT"then C2 value is "SHOW" otherwise is "HIDE"
If Cell A2 is ART
and cell B2 is "RATART" or "ART" or "ARTRAT"then C2 value is "SHOW" otherwise is "HIDE"

I have got something like this but I need to add some more conditions to get the above result
=IF(AND($L$3="RAT",M3="RATART"),"Show","Hide")
Can you help please?

 

[Desu Nota from Columbus]

=IF(AND(A2="RAT",OR(B2="RATART", B2="RAT",B2="ARTRAT")),"SHOW",IF(AND(A2="ART",OR(B2="RATART",B2="ART",B2="ARTRAT")),"SHOW","HIDE"))

does this do the trick?

 

[olympiac]

Thank you.

I was about to use the following formula but excel does not allow to much IF

=IF(AND(L3=RAT,M3=RATART),"Show",IF(AND(L3=RAT,M3=RAT),"Show",IF(AND(L3=RAT,M3=ARTRAT),"SHOW",IF(AND(L3=ART,M3=RATART),"Show",IF(AND(L3=RAT,M3=ART),"HIDE",
IF(AND(L3=ART,M3=ART),"Show",IF(AND(L3=ART,M3=ARTRAT),"SHOW",IF(AND(L3=ART,M3=RATART),"SHOW",IF(AND(L3=ART,M3=RAT),"HIDE","Not Required")))

Cheers

 

[Desu Nota from Columbus]

yeah, unfortunately excel 2003 only allows 7 if statements in one formula. A pain in the butt sometimes for sure!

 

[njimack]

Here's a 2003 version:

=IF(OR(AND(L3="RAT",M3="RATART"),AND(L3="RAT",M3="RAT"),AND(L3="RAT",M3="ARTRAT"),AND(L3="ART",M3="RATART"),AND(L3="ART",M3="ART"),AND(L3="ART",M3="ARTRAT"),AND(L3="ART",M3="RATART")),"SHOW",IF(OR(AND(L3="RAT",M3="ART"),AND(L3="ART",M3="RAT")),"HIDE","Not Required"))

 

[olympiac]

What's wrong with this one?

=IF(AND(C518=hide,C515=HIDE),"HIDE",IF(AND(C518=hide,C515=SHOW),"HIDE",IF(AND(C518=not hide,C515=SHOW),"SHOW",IF(AND(C518=not hide,C515=HIDE),"HIDE","")

 

[olympiac]

I fixed it


=IF(AND($C$517="hide",C515="HIDE"),"HIDE",IF(AND($C$517="hide",C515="SHOW"),"HIDE",IF(AND($C$517="not hide",C515="SHOW"),"SHOW",IF(AND($C$517="not hide",C515="hide"),"HIDE"))))

 

[njimack]

I fixed it


=IF(AND($C$517="hide",C515="HIDE"),"HIDE",IF(AND($C$517="hide",C515="SHOW"),"HIDE",IF(AND($C$517="not hide",C515="SHOW"),"SHOW",IF(AND($C$517="not hide",C515="hide"),"HIDE"))))

But it could be a lot more straightforward...

=IF(OR(AND($C$517="hide",C515="HIDE"),AND($C$517="hide",C515="SHOW"),AND($C$517="not hide",C515="hide")),"HIDE",IF(AND($C$517="not hide",C515="SHOW"),"SHOW","HIDE"))

 

[olympiac]

Yes it's true.

Excellent!

Thank you

 

[Scott Huish]

It looks like it could be more straightforward than that. It looks like all you are doing is seeing if the search text is contained in the other cell.

=IF(ISNUMBER(SEARCH(A2,B2)),"SHOW","HIDE")