Excel - IF function with multiple words [Solved/Closed]

- - 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 

5 replies

Best answer
approved by Jean-François Pillou on Dec 9, 2018
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
37
Thank you
try this formula

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

Say "Thank you" 37

A few words of thanks would be greatly appreciated. Add comment

CCM 2937 users have said thank you to us 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
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
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...?
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
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