Searching for two text terms in a cell to return a value

[Balmer07]

Hi,

In cell A1 I have detail like below:
"Sale - I will give a towel"

What I need is for an if formula to look for both terms "Sale" and Towel" in the cell, if both terms are there I need it to return "Sale1" If only "Sale" is in the cell I need it to return "Sale2"

I hope this makes sense, ask if you need further detail

thanks,

 

[arthurbr]

Perhaps =IF(AND(SUMPRODUCT(--ISNUMBER(FIND("towel",A1))),SUMPRODUCT(--ISNUMBER(FIND("Sale",A1)))),"Sale1",IF(SUMPRODUCT(--ISNUMBER(FIND("Sale",A1))),"Sale2",0))

(can be shortened if one of both strings is always present)

 

[Rick Rothstein]

I think this formula will also do what you want...

=IF(COUNTIFS(A1,"*sale*",A1,"*towel*"),"Sale1",IF(COUNTIF(A1,"*sale*"),"Sale2",""))

 

[jtakw]

Hi,

Just another way:

Book1
	A	B
1	Sale - I will give a towel	Sale1
2	I will give a towel
3	Sale - I will give a robe	Sale2
Sheet316

Cell Formulas
Range	Formula
B1	=IF(COUNTIF(A1,"*Sale*"),IF(COUNTIF(A1,"*Towel*"),"Sale1","Sale2"),"")