Only first of multiple If's returns results

[JR Hester]

Excel 2007 on WinXP

I need to evaluate the contents of Column F.
If the cell contaions the word "Lead", place Lead in column G
If the cell contains the word "Manager". place Manager in column G
If the cell contains the word "Supervisor", place Super in column G

I used the formula below.
=IF(FIND("Lead", F3)>0, "Lead", IF(FIND("Manager", F3)>0, "manager",
IF(FIND("Supervisor", F3)>0, "Super", "standard")))

Only those rows that contain "Lead: somewhere in in column F record Lead in
column G, all others report VALUE#. Can someone point me toward my error. It
is important for you to know that cells in column F wil have more than just
the word Lead, Manager or Supervisor; these words may be at beginning, in
middle or at the end. They will always be separate words.

Thanks for any suggestions

 

[Jim Thomlinson]

Find is case sensitive. That could be causing you a problem. Have you tried
Search which is virtually identical but is not case sensitive?

 

[T. Valko]

One way...

=IF(COUNTIF(F3,"*lead*"),"Lead",IF(COUNTIF(F3,"*manager*"),"Manager",IF(COUNTIF(F3,"*supervisor*"),"Super","Standard")))

 

[JR Hester]

Thanks to both of you,

That appears to be a very interesting use of COUNTIF, I would never have
thought of using that as opposed to FIND. Thanks again for the solution; it
saved teh day for me.

 

[T. Valko]

You're welcome. Thanks for the feedback!