If & Find Statement

B

bogi_js

I got the problem which is
cell A1 = maybe contain text -> apple, orange, pears, bubbles

I just only can set up a formula with this
=IF(FIND("Apple",A1)>0,"apple"

but I cannot add another condition into this, actually I want it ca
find orange, pears & bubbles....

Anyone can help me????? Pls pls pl
 
P

Peo Sjoblom

One way

=IF(ISNUMBER(FIND("apple",A1)),"apple",IF(ISNUMBER(FIND("orange",A1)),"orang
e",IF(ISNUMBER(FIND("pears",A1)),"pears",IF(ISNUMBER(FIND("bubbles",A1)),"bu
bbles","not found"))))

or

=IF(ISNA(MATCH(TRUE,ISNUMBER(FIND({"apple";"orange";"pears";"bubbles"},A1)),
0)),"Not
found",INDEX({"apple";"orange";"pears";"bubbles"},MATCH(TRUE,ISNUMBER(FIND({
"apple";"orange";"pears";"bubbles"},A1)),0)))
 
Top