Excel - IF function with multiple wordsSolved

Question

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!

venkat1926 · Accepted Answer

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

venkat1926 · Answer

=IF(OR(B4="apple",B4="Banana",B4="carrot"),"yes","no")

Nick · Answer

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

"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

Nick · Answer

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.

Excel - IF function with multiple words

5 responses

Your reply

Similar discussions

Subscribe To Our Newsletter!