Do I use "IF" or something else?

[klg]

I am developing a spreadsheet for us to use at for keeping track of bonuses.
I have 7 columns - date, policyholder, policy, type, premium, bonus, and
initials. The date, policyholder and premium columns we'll fill in. I have
created dropdown boxes for the policy and type columns. I want the bonus
column to fill itself based on either what is chosen in the type column or to
calculate 2% of the premium, (depending on which it used), but I can't seem
to make any function work.

I tried the following formula for the type, but it didn't work.

=IF(D5=CD/ASSISTED,CHECKING,CREDIT CARD,DECLINED/WORKED LOAN,MONEY
MARKET,MUTUAL FUND ASSISTED,SAVINGS, "10.00", IF(D5=ANNUITY IRA/TSA
ASSIST/REFERRAL,ASSURANT STUDENT,HOSPITAL INCOME,LOAN W/OUT CREIDT
DISABILITY,MEDICARE SUPPLEMENT,MORTGAGE DISABILITY INCOME,TERM,
"25.00",IF(D5= ANNUITY IRA/TSA,ASSURANT INDIVIDUAL MEDICAL,LOAN W/CREDIT
DISABILITY,LONG TERM CARE,MORTGAGE LOAN CLOSED,UNIVERSAL LIFE,WHOLE LIFE,
"50.00")))

HELP!!

 

[Niek Otten]

You should use the VLOOKUP() function.
Look here for a tutorial:

http://www.contextures.com/xlFunctions02.html

Also, you should use short codes or abbreviations for your descriptions. It is too easy to make a typo now, which you demonstrate
in your question.

Post again in this thread if you can't get it done

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I am developing a spreadsheet for us to use at for keeping track of bonuses.
| I have 7 columns - date, policyholder, policy, type, premium, bonus, and
| initials. The date, policyholder and premium columns we'll fill in. I have
| created dropdown boxes for the policy and type columns. I want the bonus
| column to fill itself based on either what is chosen in the type column or to
| calculate 2% of the premium, (depending on which it used), but I can't seem
| to make any function work.
|
| I tried the following formula for the type, but it didn't work.
|
| =IF(D5=CD/ASSISTED,CHECKING,CREDIT CARD,DECLINED/WORKED LOAN,MONEY
| MARKET,MUTUAL FUND ASSISTED,SAVINGS, "10.00", IF(D5=ANNUITY IRA/TSA
| ASSIST/REFERRAL,ASSURANT STUDENT,HOSPITAL INCOME,LOAN W/OUT CREIDT
| DISABILITY,MEDICARE SUPPLEMENT,MORTGAGE DISABILITY INCOME,TERM,
| "25.00",IF(D5= ANNUITY IRA/TSA,ASSURANT INDIVIDUAL MEDICAL,LOAN W/CREDIT
| DISABILITY,LONG TERM CARE,MORTGAGE LOAN CLOSED,UNIVERSAL LIFE,WHOLE LIFE,
| "50.00")))
|
| HELP!!

 

[Tyro]

You have to put your text - e.g. CD/ASSISTED in quotes. Excel thinks you're
trying to divide (/) something called CD by something called ASSISTED.
Correct is "CD/ASSISTED". But in any event a vlookup table is preferable.

Tyro

 

[klg]

Thank you soooo much for your response. I have been attempting the VLOOKUP
as you said for the last couple of hours and I can't get it to work either.
I feel really stupid....

 

[Tyro]

As in the examples in Excel help, you create entries in A1 through A3 with
the values (A1) CD/ASSISTED, (A2) CHECKING, (A3) CREDIT CARD etc. Then in
column B, (B1) "10.00", (B2) "10.00", (B3) "10.00" etc. Then in say, D5 you
enter what you are looking for, say, checking. Then in D5 you enter the
formula =vlookup(D5,A1:B3,2,FALSE). The formula says to look up whatever is
in D5, e.g. checking in A1:A3 and if an exact case insensitive match is
found (FALSE), as it will be in A2, return the value in the second column
(the 2), in this case B2, "10.00". If you want numeric values returned just
enter 10 instead of "10.00" The values in column A must be unique.

Tyro

 

[Tyro]

Correction Change the reference to "Then in D5 you enter" To "Then in D6 you
enter". You cannot have a formula return a value on top of itself. I never
see these mistakes until I have posted.

Tyro

 

[klg]

I thought I understood, evidently I don't. I wish I had never volunteered to
do this, it's more involved than anything I've ever done before. I am
supposed to submit this tomorrow morning, brilliant huh?

I have two worksheets, one for calculating the information and the second
one with the reference information. I used
=VLOOKUP(D5,Policies!,C3:C23,2,FALSE
but it didn't work. You probably don't have enough information huh?
AAAARRRRGGGGHHHH!!!!

 

[Dave Peterson]

I didn't read the whole thread, but this formula:

=VLOOKUP(D5,Policies!C3:C23,2,FALSE)
can't work. You're trying to return the second column of the C3:C23 range--and
that's just one column. (As well as correcting the typo with the extra comma.)

Maybe you want:
=VLOOKUP(D5,Policies!C323,2,FALSE)

 

[klg]

thank you!! he gave me an extension until tomorrow. hopefully you or
someone can help me. the url is http://www.savefile.com/files/1331535

 

[Tyro]

You're using a formula =VLOOKUP(D5,Policies!C3:C23,2,FALSE) You are using a
lookup array of only one column C3:C23 and attempting to return a value from
column 2. There is no column 2. You have to include column D where the
corresponding return values are for the entries in column C. Thus the
formula should be =VLOOKUP(D5,Policies!C323,2,FALSE) That formula works
fine

Tyro