Multiple IF AND OR Statements

[nmerchant]

I'm trying to nest multiple IF statements, along with IF(AND(OR) statements within one cell. The "Premium Table" I have to use is below, along with a sample of the the worksheet. The question is: What IF statement do I enter in cell D2 to obtain the correct premium?

Premium Table

Age Range - Coverage Type - Premium Amount

Ages 18-39 <colgroup><col><col><col></colgroup><tbody> </tbody>	Employee Only Employee + Spouse Employee + Child(ren) Employee + Family	$76.05 $366.19 $366.19 $366.19
Ages 40-49	Employee Only Employee + Spouse Employee + Child(ren) Employee + Family	$111.20 $418.94 $418.94 $418.94
Ages 50-59	Employee Only Employee + Spouse Employee + Child(ren) Employee + Family	$234.32 $586.02 $586.02 $586.02
Ages 60+	Employee Only Employee + Spouse Employee + Child(ren) Employee + Family	$329.20 $821.56 $821.56 $821.56

<tbody>
</tbody>

	A	B	C	D
1	Employee Name	Age	Coverage	Premium
2	John Doe	35	Employee + Family
3	Jane Smith	41	Employee Only
4	Bob Johnson	60	Employee + Spouse

<tbody>
</tbody>

 

[DRSteele]

Welcome to the Forum.

I suggest you alter the layout and use functions INDEX and MATCH, which is much easier than nesting IF statements. Copy D11 down for each Employee enquiry.

	A	B	C	D	E
1	Premium Table
2	Age Range	Employee Only	Employee + Spouse	Employee + Child(ren)	Employee + Family
3	18	$76.05	$366.19	$366.19	$366.19
4	40	$111.20	$418.94	$418.94	$418.94
5	50	$234.32	$586.02	$586.02	$586.02
6	60	$329.20	$821.56	$821.56	$821.56
7
8
9
10	Employee Name	Age	Coverage	Premium
11	John Doe	35	Employee + Family	$366.19
12	Jane Smith	41	Employee Only	$111.20
13	Bob Johnson	60	Employee + Spouse	$821.56

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet37​

Worksheet Formulas Cell Formula D11 =INDEX($B$3:$E$6,MATCH(B11,$A$3:$A$6,1),MATCH(C11,$B$2:$E$2,0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Scott T]

Try

Book1
	A	B	C	D	E	F	G	H	I
1	Employee Name	Age	Coverage	Premium		Age Range - Coverage Type - Premium Amount
2	John Doe	35	Employee + Family	366.19		Ages 18-39	18	Employee Only	$76.05
3	Jane Smith	41	Employee Only	111.2			18	Employee + Spouse	$366.19
4	Bob Johnson	60	Employee + Spouse	821.56			18	Employee + Child(ren)	$366.19
5							18	Employee + Family	$366.19
6						Ages 40-49	40	Employee Only	$111.20
7							40	Employee + Spouse	$418.94
8							40	Employee + Child(ren)	$418.94
9							40	Employee + Family	$418.94
10						Ages 50-59	50	Employee Only	$234.32
11							50	Employee + Spouse	$586.02
12							50	Employee + Child(ren)	$586.02
13							50	Employee + Family	$586.02
14						Ages 60+	60	Employee Only	$329.20
15							60	Employee + Spouse	$821.56
16							60	Employee + Child(ren)	$821.56
17							60	Employee + Family	$821.56
Sheet1

Cell Formulas
Range	Formula
D2	{=INDEX($I$2:$I$17,MATCH(VLOOKUP(B2,$G$2:$G$17,1)&C2,$G$2:$G$17&$H$2:$H$17,0))}
D3	{=INDEX($I$2:$I$17,MATCH(VLOOKUP(B3,$G$2:$G$17,1)&C3,$G$2:$G$17&$H$2:$H$17,0))}
D4	{=INDEX($I$2:$I$17,MATCH(VLOOKUP(B4,$G$2:$G$17,1)&C4,$G$2:$G$17&$H$2:$H$17,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[nmerchant]

Thank you DRSTEELE and SCOTT T. Both options work great, and save a lot of time rather than nesting multiple IF statements.

 

[DRSteele]

Terrific. You're welcome.