IF Formula - Contains X and Y, also Does Not Contain U,V,W

[JExcel123]

Hello! I'm trying to write two different formulas that show the following:

Typing in Cell B1...If Cell A1 contains "Apple" AND "Orange", but also DOES NOT contain "Banana" OR "Grape" OR "Pear", then return the value in cell D1, otherwise, return a blank.

Typing in Cell C1...If Cell A1 contains "Apple" AND "Orange", and ALSO contains "Banana" OR "Grape" OR "Pear", then return the value in cell D1, otherwise, return a blank.

I came up with this so far for the second set of criteria, but Excel won't accept it...not sure if I'm even on the right track...


=IF(AND(ISNUMBER(SEARCH("Apple",A1)),ISNUMBER(SEARCH("Orange",A1))),IF(OR(ISNUMBER(SEARCH("Banana",A1)),ISNUMBER(SEARCH("Grape",A1)),ISNUMBER(SEARCH("Pear",A1)))),D1,"")

 

[Aladin Akyurek]

What do we have in D1 - a number or text?

 

[dreid1011]

Welcome to the board. It looks like your false outcome for the whole thing is in the wrong spot:

This:
=IF(AND(ISNUMBER(SEARCH("Apple",A1)),ISNUMBER(SEARCH("Orange",A1))),IF(OR(ISNUMBER(SEARCH("Banana",A1)),ISNUMBER(SEARCH("Grape",A1)),ISNUMBER(SEARCH("Pear",A1)))),D1,"")
To this:
=IF(AND(ISNUMBER(SEARCH("Apple",A1)),ISNUMBER(SEARCH("Orange",A1))),IF(OR(ISNUMBER(SEARCH("Banana",A1)),ISNUMBER(SEARCH("Grape",A1)),ISNUMBER(SEARCH("Pear",A1))),D1,""))

 

[JExcel123]

D1 is a number

 

[JExcel123]

Yay! That worked!!! The only issue is it either returns the value in D1 or it returns FALSE. How can I make it return blank rather than FALSE?

 

[Aladin Akyurek]

D1 is a number

1.

=IF((SUM(ISNUMBER(SEARCH(" "&{"apple","orange"}," "&A1))+0)=2)*(1-ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(" "&{"banana","grape","pear"}," "&A1)))),D1,"")

2.

=IF((SUM(ISNUMBER(SEARCH(" "&{"apple","orange"}," "&A1))+0)=2)*ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(" "&{"banana","grape","pear"}," "&A1))),D1,"")

 

[dreid1011]

Yay! That worked!!! The only issue is it either returns the value in D1 or it returns FALSE. How can I make it return blank rather than FALSE?

Then this:

=IF(AND(ISNUMBER(SEARCH("Apple",A1)),ISNUMBER(SEARCH("Orange",A1))),IF(OR(ISNUMBER(SEARCH("Banana",A1)),ISNUMBER(SEARCH("Grape",A1)),ISNUMBER(SEARCH("Pear",A1))),D1,""),"")

 

[JExcel123]

TAHNK YOU!!!! Fantastic!!! I added a NOT to meet criteria one and that worked too! So glad I asked!!!

 

[Aladin Akyurek]

Post #6 offers you solutions which require lesser numbers of function calls if that matters...

 

[JExcel123]

Thank you, Aladin! That one did work as well! Spoiled for choice