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

Report
-
 user -
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!

5 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
try this formula

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

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

CCM 2942 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
I've been on this for weeks!
Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
=IF(OR(B4="apple",B4="Banana",B4="carrot"),"yes","no")
you need to use ; instead of ,
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.
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
791
"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