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
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 239"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Ages 18-39[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Employee Only
Employee + Spouse
Employee + Child(ren)
Employee + Family[/TD]
[TD]$76.05
$366.19
$366.19
$366.19[/TD]
[/TR]
[TR]
[TD]Ages 40-49[/TD]
[TD]Employee Only
Employee + Spouse
Employee + Child(ren)
Employee + Family[/TD]
[TD]$111.20
$418.94
$418.94
$418.94[/TD]
[/TR]
[TR]
[TD]Ages 50-59[/TD]
[TD]Employee Only
Employee + Spouse
Employee + Child(ren)
Employee + Family[/TD]
[TD]$234.32
$586.02
$586.02
$586.02[/TD]
[/TR]
[TR]
[TD]Ages 60+[/TD]
[TD]Employee Only
Employee + Spouse
Employee + Child(ren)
Employee + Family[/TD]
[TD]$329.20
$821.56
$821.56
$821.56[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Employee Name[/TD]
[TD="align: center"]Age[/TD]
[TD="align: center"]Coverage[/TD]
[TD="align: center"]Premium[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]John Doe[/TD]
[TD]35[/TD]
[TD]Employee + Family[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Jane Smith[/TD]
[TD]41[/TD]
[TD]Employee Only[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Bob Johnson[/TD]
[TD]60[/TD]
[TD]Employee + Spouse[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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
John Doe	Employee + Family
Jane Smith	Employee Only
Bob Johnson	Employee + Spouse

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #F8CBAD"]Premium Table[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #FCE4D6"]Age Range[/TD]
[TD="bgcolor: #FCE4D6"]Employee Only[/TD]
[TD="bgcolor: #FCE4D6"]Employee + Spouse[/TD]
[TD="bgcolor: #FCE4D6"]Employee + Child(ren)[/TD]
[TD="bgcolor: #FCE4D6"]Employee + Family[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]$76.05[/TD]
[TD="align: right"]$366.19[/TD]
[TD="align: right"]$366.19[/TD]
[TD="align: right"]$366.19[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]$111.20[/TD]
[TD="align: right"]$418.94[/TD]
[TD="align: right"]$418.94[/TD]
[TD="align: right"]$418.94[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]$234.32[/TD]
[TD="align: right"]$586.02[/TD]
[TD="align: right"]$586.02[/TD]
[TD="align: right"]$586.02[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]60[/TD]
[TD="align: right"]$329.20[/TD]
[TD="align: right"]$821.56[/TD]
[TD="align: right"]$821.56[/TD]
[TD="align: right"]$821.56[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #FFF2CC"]Employee Name[/TD]
[TD="bgcolor: #FFF2CC"]Age[/TD]
[TD="bgcolor: #FFF2CC"]Coverage[/TD]
[TD="bgcolor: #FFF2CC"]Premium[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]35[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]$366.19[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]41[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]$111.20[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]60[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]$821.56[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet37​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D11[/TH]
[TD="align: left"]=INDEX($B$3:$E$6,MATCH(B11,$A$3:$A$6,1),MATCH(C11,$B$2:$E$2,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.