Report

Excel - IF function with multiple words [Closed]

Ask a question Nick - Last answered on Aug 1, 2017 at 02:00 PM by Mike
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!
Helpful
+36
plus moins
try this formula

=IF(OR(ISNUMBER(SEARCH("apple",B4)),ISNUMBER(SEARCH("banana",B4)),ISNUMBER(SEARCH("carrot",B4))),"yes","no")
Was this answer helpful?  
vj- Jul 23, 2014 at 10:12 PM
txs man its working good
bc622- Jan 8, 2015 at 12:38 AM
Very helpful!
pharm- Jan 18, 2015 at 05:10 PM
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")
Mike- Aug 1, 2017 at 02:00 PM
Thanks! Very helpful!
Reply
Helpful
+25
plus moins
=IF(OR(B4="apple",B4="Banana",B4="carrot"),"yes","no")
so bored- Aug 19, 2014 at 07:35 AM
you need to use ; instead of ,
Helpful
+11
plus moins
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.
Helpful
+7
plus moins
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...?
Helpful
+3
plus moins
"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

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!