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

JExcel123

New Member
Joined
Oct 4, 2017
Messages
5
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,"")
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

dreid1011

Well-known Member
Joined
Jun 4, 2015
Messages
2,129
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

New Member
Joined
Oct 4, 2017
Messages
5

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

Well-known Member
Joined
Jun 4, 2015
Messages
2,129

ADVERTISEMENT

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

New Member
Joined
Oct 4, 2017
Messages
5
TAHNK YOU!!!! Fantastic!!! I added a NOT to meet criteria one and that worked too! So glad I asked!!! :)
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Post #6 offers you solutions which require lesser numbers of function calls if that matters...
 

Watch MrExcel Video

Forum statistics

Threads
1,118,192
Messages
5,570,771
Members
412,340
Latest member
nikitesh95
Top