IF statement that checks for data in two different cells

[jmpatrick]

I've created a formula that checks for partial data matches in two different cells, but it's giving me a "too many arguments" error:

=IF(OR(ISNUMBER(SEARCH({"*1072*","*1087*"},$C5))),"No BOM","BOM XL",IF(OR(ISNUMBER(SEARCH({"*"},$BP5))),"No BOM","BOM XL"))

I want to check for the presence of data containing 1072 or 1087 in $C5 and also check for an asterisk * in $BP5. So if either 1072 or 1087 appear in $C5 or an asterisk appears in $BP5 the result is "No BOM", otherwise it's "BOM XL".

Make sense?

 

[Fluff]

How about
=IF(OR(ISNUMBER(SEARCH({"*1072*","*1087*"},$C5)),ISNUMBER(SEARCH("*",$BP5))),"No BOM","BOM XL")

 

[jmpatrick]

How about
=IF(OR(ISNUMBER(SEARCH({"*1072*","*1087*"},$C5)),ISNUMBER(SEARCH("*",$BP5))),"No BOM","BOM XL")

Thanks for the quick response. You got closer than I did! No error message now, but it only works with 1072 and 1087. If there's an asterisk in $BP5 it doesn't respond.

 

[jmpatrick]

I have to correct my response. What I'm getting is "No BOM" when 1072 and 1087 is present in $C5, but only after I changed the formula to:

=IF(OR(ISNUMBER(SEARCH({"*1072*","*1087*"},$C5)),ISNUMBER(SEARCH("*",$BP5))),"BOM XL","No BOM")

If there's an asterisk in $BP5 it doesn't respond.

 

[Fluff]

Do you want No BOM or BOM XL if c5 has 1072 or 1087?

 

[jmpatrick]

Do you want No BOM or BOM XL if c5 has 1072 or 1087?

"No BOM" if c5 has 1072 or 1087 or if bp5 has an asterisk, otherwise "BOM XL".

Thanks for the reply.

 

[Fluff]

Ok, try
=IF(OR(ISNUMBER(SEARCH({"*1072*","*1087*"},$C5)),ISNUMBER(SEARCH("~*",$BP5))),"No BOM","BOM XL")

 

[jmpatrick]

Thanks!

 

[Fluff]

You're welcome.