Searching for a function

  • Thread starter Thread starter Anonymoose
  • Start date Start date
A

Anonymoose

I've got a column that includes a code such as LMD and
either an E for employee or a F for family. The LMD
would signify life, medical and dental. I have looked
through the functions and can't find one to use with a
nested IF to look in the cell with the codes and if it
inludes an "L" for instance, that would produce a value
in the Life Insurance column from a formula I use if
True. But the tricky part is I have to be able to have
it also identify that E or F because of the different
fees associated with it. I cannot take apart that cell
either. The other tricky part is I cannot use one of the
Right or Mid functions because one cell might be LMDE
while the other could be DF.

Any ideas?
 
Use the FIND function.

Here is an example to check for Life

=IF(ISERROR(FIND("L",A20)),"","Life")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thanks for the information. Using your help I came up
with =IF(RIGHT(A6,1)="1",IF(FIND("L",I6),(((N6*2.5)/1000)
*2.1)*20%,0),0)I use the Right function to tell me if it
is a Salaried employee -- only salaried employees get the
life insurance benefit. What has to happen is if there
is an "L" then that Life Insurance column calculates the
employees payment. This formula works except I get the
#VALUE! result if a column should have other information
such as just MDE without the L so everything is working
except for that piece. Any ideas how I can avoid that
#VALUE!?

Thanks for your help!

Amy
 
Amy,

You should have used ISERROR as I showed you

=IF(RIGHT(A6,1)="1",IF(NOT(ISERROR(FIND("L",I6))),(((N6*2.5)/1000)*2.1)*20%,
0),0)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
I haven't had any experience with ISERROR so I was trying
to do what I knew. Just now I investigated that function
and it hides those #VALUE! results. How cool... thanks
for the help!
 
Back
Top