Only first of multiple If's returns results

  • Thread starter Thread starter JR Hester
  • Start date Start date
J

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
 
Find is case sensitive. That could be causing you a problem. Have you tried
Search which is virtually identical but is not case sensitive?
 
One way...

=IF(COUNTIF(F3,"*lead*"),"Lead",IF(COUNTIF(F3,"*manager*"),"Manager",IF(COUNTIF(F3,"*supervisor*"),"Super","Standard")))
 
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.
 
Back
Top