Suggest an Formula

[shaan.bhasin]

Hi

Kindly go through the following table. I want to return the Premium amount in other worksheet where inputs will be age and Term and in 3rd column after entering these two inputs the third can be derived from this table through formula. Kindly suggest the formula through which this can be achieved.

This table can be modified accordingly.



Age - Term - Premium
22 - 10 - 190
22 - 15 - 185
23 - 10 - 195
23 - 15 - 190
24 - 10 - 200
24 - 15 - 195
25 - 10 - 205
25 - 15 - 200
26 - 10 - 210
26 - 15 - 205


Thanks in Advance.

 

[jasonb75]

Personally I would use something like

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 71px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt">Age/Term</TD><TD style="TEXT-ALIGN: right">10</TD><TD style="TEXT-ALIGN: right">15</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">22</TD><TD style="TEXT-ALIGN: right">185</TD><TD style="TEXT-ALIGN: right">190</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">23</TD><TD style="TEXT-ALIGN: right">190</TD><TD style="TEXT-ALIGN: right">195</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">24</TD><TD style="TEXT-ALIGN: right">195</TD><TD style="TEXT-ALIGN: right">200</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">25</TD><TD style="TEXT-ALIGN: right">200</TD><TD style="TEXT-ALIGN: right">205</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana; FONT-SIZE: 10pt">26</TD><TD style="TEXT-ALIGN: right">205</TD><TD style="TEXT-ALIGN: right">210</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="FONT-FAMILY: Verdana; FONT-SIZE: 10pt"> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>Age</TD><TD>Term</TD><TD>Premium</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right">23</TD><TD style="TEXT-ALIGN: right">15</TD><TD style="TEXT-ALIGN: right">195</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C10</TD><TD>=INDEX($B$2:$C$6,MATCH(A10,$A$2:$A$6,0),MATCH(B10,$B$1:$C$1,0))</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1

Other users may have alternative suggestions.

 

[jim may]

Depending on whether this is acceptible to you, or not..
Use VLOOKUP() - and here I'm concatenating your two criteria as the Lookup Value;
It requires that you create a helper column (which you can hide) to create the matching Lookup value in the Lookup table...

FWIW,

Jim

Excel Workbook
	A	B	C
1	Age	Term	Premium
2	22	10	190
3	15	185
4	23	10	195
5	15	190
6	24	10	200
7	15	195
8	25	10	205
9	15	200
10	26	10	210
11	15	205
Sheet1

Excel 2007

Cell Formulas
Range	Formula
C2	=IF(VLOOKUP(A2&B2,Sheet2!C1:D2,2,FALSE)=0,"",VLOOKUP(A2&B2,Sheet2!C1:D2,2,FALSE))

Excel Workbook
	A	B	C	D
1	Age	Term	Concatenate_COL_A and Col_B	Premium
2	22	10	2210	190
3	15	185	15185
4	23	10	2310	195
5	15	190	15190
6	24	10	2410	200
7	15	195	15195
8	25	10	2510	205
9	15	200	15200
10	26	10	2610	210
11	15	205	15205
Sheet2

Excel 2007

Cell Formulas
Range	Formula
C2	=A2&B2

 

[rrajansg]

Try sumproduct formula

Formula in C10
=sumproduct((a10=a2:a6)*(b10=b2:b6)*(c2:c6))

 

[shaan.bhasin]

Thanks Jason and Jim both of you have suggested a good solution.
As i mentioned i can modify the table so, Jason's was little easy to go on with.
thanks both of you for a prompt reply =)