Excel - IF function with multiple words [Closed]

Nick - Jan 31, 2012 at 03:56 AM - Latest reply:  Charls007
- Feb 15, 2018 at 08:42 AM
Hi

I'm trying to write a formula to produce a yes/no answer depending on whether the data in a previous cell in the same row contains any of the words I am trying to match.

Ie. I want the formula cell to read "Yes" if Cell B4 contains the words "Apple" or "Banana" or "Carrot", or "No" if it doesn't contain any of those words.

I am currently trying something like:

=COUNTIF(OR(B4,"*Apple*",B4,"*Banana*",B4,"*Carrot*")), "Yes","No"

(although this is rejected)

Help would be very much appreciated!
See more 

13 replies

Best answer
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Jan 31, 2012 at 10:21 PM
36
Thank you
try this formula

=IF(OR(ISNUMBER(SEARCH("apple",B4)),ISNUMBER(SEARCH("banana",B4)),ISNUMBER(SEARCH("carrot",B4))),"yes","no")

Thank you, venkat1926 36

Something to say? Add comment

CCM has helped 2009 users this month

Very helpful!
I have a similar issue but it's not working. Please help! Here is my formula: =IF(OR(ISNUMBER(SEARCH("melarsoprol",B2)),ISNUMBER(SEARCH("arsobal",B2)),ISNUMBER(SEARCH("melarsen",B2)),ISNUMBER(SEARCH("oxide-bal",B2)),ISNUMBER(SEARCH("mel-b",B2))),"Include","Exclude")
Thanks! Very helpful!
But i want this search to be performed in the different cells like ("melarsoprol",b2)),ISNUMBER(SEARCH("arsobal",C2)) this too need to match, if not matching then i want no as output. Please help
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Jan 31, 2012 at 05:28 AM
27
Thank you
=IF(OR(B4="apple",B4="Banana",B4="carrot"),"yes","no")
you need to use ; instead of ,
11
Thank you
Ie. It gives "No" when "apple" (for example) is in the cell, because other words are also in the cell... for example "Red apple" will show as "No" because the cell doesn't = "apple", it contains "apple".

I meant wildcard as in searching for "apple" among all words in the cell.
7
Thank you
Thanks venkat.

That is giving me a negative response where I should have a positive response, as the cell may contain words other than "apple" or "banana" or "carrot" aswell...

I think I need some kind of "wildcard" or "contains" search...?
venkat1926 1865 Posts Sunday June 14, 2009Registration dateContributorStatus July 30, 2015 Last seen - Jan 31, 2012 at 07:58 AM
3
Thank you
"giving me a negative response" please explain with example

wildcard means some common phrases = nothing between apple and Banana

make your requirements little more clearer.
I wonder whether excel can work like a spell check