Need to add another condition to existing function...but where?

[jmpatrick]

Here's what I have:

=IF(OR(ISNUMBER(SEARCH({"*1114M*","*1110*","*1109M*","*1091*"},$C5))),"No Floors",IF(OR(ISNUMBER(SEARCH({"SLAB - GL","SLAB - GR","SLAB"},H5))),"Slab",IF(OR(ISNUMBER(SEARCH({"Open Web"},$H5))),"Open Web","Floor Plans"))))

This works but I need to add one more condition, a check to see if an asterisk is present in $BR5. If it is then "Floor Plans" should be displayed. Here's the additional code but I'm not sure where to insert it:

,IF(OR(ISNUMBER(SEARCH("~*",$BR5))),"Floor Plans"

What seems to be tripping me up is "Floor Plans" should be the result if NONE of the conditions are met.

I'm stumped.

 

[JimM]

You've contradicted yourself! In your first statement you want "Floor Plans" if there is an asterisk in BR5 but in the second statement you want "Floor Plans" if none of the conditions are met?

 

[Fluff]

What should happen if BR5 does not contain an asterix?

 

[jmpatrick]

What should happen if BR5 does not contain an asterix?

"No Floors"

 

[Fluff]

Ok, how about

=IF(OR(ISNUMBER(SEARCH({"*1114M*","*1110*","*1109M*","*1091*"},$C5)),ISERR(SEARCH("~*",BR5))),"No Floors",IF(OR(ISNUMBER(SEARCH({"SLAB - GL","SLAB - GR","SLAB"},H5))),"Slab",IF(ISNUMBER(SEARCH({"Open Web"},$H5)),"Open Web","Floor Plans")))

 

[jmpatrick]

Ok, how about

=IF(OR(ISNUMBER(SEARCH({"*1114M*","*1110*","*1109M*","*1091*"},$C5)),ISERR(SEARCH("~*",BR5))),"No Floors",IF(OR(ISNUMBER(SEARCH({"SLAB - GL","SLAB - GR","SLAB"},H5))),"Slab",IF(ISNUMBER(SEARCH({"Open Web"},$H5)),"Open Web","Floor Plans")))

All conditions result in No Floors except the asterisk.

 

[Fluff]

If you have an asterisk in BR5 & Slab - GL in H5 then you will get Slab.
Is that not what you want?

 

[jmpatrick]

If you have an asterisk in BR5 & Slab - GL in H5 then you will get Slab.
Is that not what you want?

Sorry, I should have been more specific in my reply.

- The default (any number but the four listed below) should be: Floor Plans. This is not working. I'm getting No Floors no matter the number in C5.
- If 1114M, 1110, 1109M, or 1091 appear anywhere in C5 I should get No Floors (which is not working), EXCEPT when an asterisk is in BR5. If there is an asterisk in BR5 I should get Floor Plans, which IS working correctly.
- You are correct, if SLAB - GL, SLAB - GR, or SLAB appear in H5 I should get SLAB. This is not working.

Thanks for all your help.

 

[Fluff]

So if there is an asterisk in BR5 it overules all other criteria?

 

[jmpatrick]

So if there is an asterisk in BR5 it overules all other criteria?

Correct.