How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Jack Your Own Question
Jack
Jack, Computer Software Engineer
Category: Microsoft Office
Satisfied Customers: 146
Experience:  11 yrs System Engineering
26652636
Type Your Microsoft Office Question Here...
Jack is online now
A new question is answered every 9 seconds

I am not a power user, but not a novice to excel either. I

Customer Question

I am not a power user, but not a novice to excel either. I need help writing a formula that lets my staff enter an Employee's "AGE" and in another column the Employee's Coverage Type which can be one of 4 types - "EE","ES","EC", or "FAMILY), and based on age/coverage type the field will display the correct rate "result" from the Table. Example of Table:

AGE EE ES EC FAMILY
0-29 319(NNN) NNN-NNNN
30-39 353(NNN) NNN-NNNN
Submitted: 4 years ago.
Category: Microsoft Office
Expert:  Jack replied 4 years ago.
  • you can use a lookup table or a nested IF(AND(,,),,) formula in column C like:
  • where cell a1 is age and cell b1 is coverage type
  • Note for values between 0 an 1 use a decimal like .1 or .000001
  • Formula is

=IF( AND(A1>0,A1<30,B1="EE"),319,IF(AND(A1>29,A1<40,B1="EE"),353,IF(AND(A1>0,A1<30,B1="ES"),819,IF(AND(A1>29,A1<40,,B1="ES"),959,IF(AND(A1>0,A1<30,B1="EC"),877,IF(AND(A1>29,A1<40,B1="EC"),902,IF(AND(A1>0,A1<30,B1="Family"),1240,IF(AND(A1>29,A1<40,B1="Family"),1372,"bad entry")

Customer: replied 4 years ago.

Thanks, but the problem with this formula is that I would have too much data to enter in the formula. Another user posted this formula which I ended up using. It goes like this:


=INDEX(B2:E8),MATCH(G2,AS:A8,0),MATCH(G3,B1:E1,0))

Related Microsoft Office Questions