Trying to find a word and put a 1 in another column

[rpage]

I have searched and found formulas but cannot get it to work. I simply want to search for a word e.g. CIO in a job title and then put a 1 in the column next door...

Sounds simple but despite trying heaps of websites cannot do it. I managed to do it but if the string is not present I get #VALUE rather than a blank.

Help appreciated.

[Haseeb Avarakkan]

Hello rpage,

Assume your title is in A1, B1 try,

=IF(ISNUMBER(SEARCH("CIO",A1)),1,"")

Is this not you are looking, please attach a sample file & your expecting outcome.

[rpage]

Excellent! It works! Thanks really appreciated! Blessings on you!

If I want to include extra words how would the formula be expanded?

[daddylonglegs]

Say you want a 1 if any of the following are in the cell

Apple
Banana
Lemon

Then try this formula

=IF(COUNT(SEARCH({"Apple","Banana","Lemon"},A1)),1,"")

.....or list the "fruits" in a range of cells, e.g. K2:K4 and use

=IF(COUNT(INDEX(SEARCH(K2:K4,A1),0)),1,"")