Excel - IF and SEARCH functions

Question

Let's say I have 6 cells containing these words respectively:

RICE CAKE (T)
COOKING OIL (T)
SUGAR ROLL (T)
CENTURY EGGS (T)
SALTED EGGS (T)
LARGE EGGS (E)

I used this formula but it did not work fully. =IF(COUNT(SEARCH({"*oil*","salted*","*sugar*","*rice*","*century*"},A1)),"T",FALSE)

What should I add in the formula to make sure that cell 6 would return value of "E"?
RICE, OIL, SUGAR, CENTURY, SALTED should return a value of "T" (as shown in my formula)
EGGS alone should return a value of "E" (like in cell number 6 WITHOUT coinciding with cell number 4 and 5 which contains eggs as well but they should be T)

How do I use any formula to return the correct value.

barry houdini · Accepted Answer · 2012-06-22 15:24:15Z

1

You can try this version

=IF(SUM(COUNTIF(H222,"*"&{"Rice","Oil","sugar","Century","Salted"}&"*")),"T",IF(ISNUMBER(SEARCH("eggs",H222)),"E",""))

Add "Quail" to the first part if required

Edit: I didn't see your edit - I think that's a version I suggested elsewhere (with COUNT). That should work, what problem did you get? You don't need the "wildcards" with SEARCH (you do with COUNTIF) so that could be like this

=IF(COUNT(SEARCH({"oil","salted","sugar","rice","century"},A1)),"T",IF(ISNUMBER(SEARCH("eggs",A1)),"E",""))

answered Jun 22, 2012 at 15:24

barry houdini

46.9k8 gold badges67 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sean Foo Over a year ago

wow thank you very much! how do you know the cell I am gonna use is H222? :O

Sean Foo Over a year ago

oh i was wondering how come! anyways thank you very much barry :) glad for your help! much appreciated!

Siddharth Rout Over a year ago

+ 1 Another nice formula Barry :)

Collectives™ on Stack Overflow

Excel - IF and SEARCH functions

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related