Premium Formula

[Mallory]

I have created a spreadsheet for my clients and would like a formula to calculate the premium.

There are 4 Packages to choose from:

Core
Bronze
Silver
Gold

Premiums differ in each package according to the clients age.

I have a column with the clients age and I have a column which confirms the package and then I have tables that confirm give the premium

What formula can I use that will identify the plan and then give the premium based on the clients age.

 

[Dave Patton]

You can use Choose. You can use the Table or Name Manager to hold the Lookup information.
Complete and check the numbers for the Table.
Complete and add the Names if you use that alternative.
Names like
Plan ={"Bronze","Core","Gold","Silver"}
Age ={18;31;46;51;56}
Bronze ={107.61;118.16;130.82;144.54;159.31} or use named ranges from the Table see below

T202012b.xlsm
	A	B	C	D	E	F	G	H	I
1	Age	Plan	Amount
2	60	Silver	239.49
3	24	Bronze	107.61		Age	Bronze	Core	Gold	Silver
4					18	107.61			163.53
5					31	118.16			179.35
6					46	130.82			197.29
7					51	144.54			217.33
8					56	159.31			239.49
1f

Cell Formulas
Range	Formula
C2	C2	=LOOKUP(A2,$E$4:$E$8,CHOOSE(MATCH(B2,$F$3:$I$3,0),$F$4:$F$8,$G$4:$G$8,$H$4:$H$8,$I$4:$I$8))
C3	C3	=LOOKUP(A3,Age,CHOOSE(MATCH(B3,Plan,0),Bronze,Core,Gold,Silver))

Named Ranges
Name	Refers To	Cells
Bronze	='1f'!$F$4:$F$8	C2:C3
Core	='1f'!$G$4:$G$8	C2:C3
Gold	='1f'!$H$4:$H$8	C2:C3
Silver	='1f'!$I$4:$I$8	C2:C3