index & match

[prlondhe]

Hi

Iam trying to find the rate for age group (approx Match) & value (exact match) from the following table with the following formula.

age=55
amount=200000

INDEX($C$21:$R$31,MATCH(55,$B$20:$B$31,TRUE),MATCH(200000,$C$20:$R$20,FALSE))

the value returned in 8800 instead of 8000.

Can anyone please help

AGE	100000	125000	150000	175000	200000	225000	250000	275000	300000	350000	400000	450000	500000	600000	700000	800000
5	1650	2000	2300	2700	3000	3400	3600	4000	4200	4700	5200	5700	6200	7000	7800	8500
25	1500	1900	2300	2600	2900	3200	3400	3700	4000	4500	4900	5500	6000	7000	7500	8000
30	1600	2000	2350	2650	3000	3300	3500	3800	4100	4600	5000	5600	6200	7200	8000	8800
35	1650	2050	2400	2700	3050	3350	3600	4000	4300	4900	5300	5900	6400	7500	8500	9000
40	1950	2350	2750	3100	3400	3800	4100	4450	4800	5400	6000	6500	7000	7700	8700	9300
45	2600	3100	3500	4000	4400	4700	5200	5700	6200	7000	7700	8500	9200	10500	11500	12500
50	3700	4500	5200	6100	6800	7500	8200	8900	9700	11000	12200	13800	15000	17000	20000	22000
55	4300	5200	6000	7000	8000	9000	9800	10400	11500	12500	14500	16000	17500	20000	23000	25000
60	4800	5800	6900	8000	8800	10000	10500	11600	13000	14800	16500	18000	20000	23000	26000	30000
65	5400	6500	7700	9000	10200	11600	12600	13700	14700	16700	19000	21000	23000	25000	30000	33000
70	6200	7200	8500	10000	11400	12800	14000	15000	16500	18800	21000	23500	26000	30000	35000	40000

<colgroup><col><col><col><col span="2"><col><col><col><col><col span="8"></colgroup><tbody>
</tbody>

 

[jasonb75]

Try

=INDEX($C$21:$R$31,MATCH(55,$B$21:$B$31,TRUE),MATCH(200000,$C$20:$R$20,FALSE))

 

[prlondhe]

thank you for your reply but now age 57 also giving the same value

 

[jasonb75]

Based on your table and the way excel works, 57 and 200000 should return 8000.

What row should be used for under age 5?
What row for over 70?

For ages between those listed should the age be rounded up, rounded down, or rounded to nearest?

 

[prlondhe]

Sir,

These are age brackets starting from 0 to 5 , 6 to 25, 26 to 35, 36 to 40 and so on 70 >

 

[jasonb75]

Based on that logic, the last row would be for 66 to 70, so should ages above 70 be ignored?

Re-sort your table so that age 70 is at the top, age 5 at the bottom, then change the formula to

=INDEX($C$21:$R$31,MATCH(55,$B$21:$B$31,-1),MATCH(200000,$C$20:$R$20,FALSE))

 

[prlondhe]

sir

thank you very much. but for age groups above 70 the value has to be taken+2%.

eg. for age > 70 and value = 200000

the value should be 11400+11400*2%

Any further suggestions would be welcome. Thank you once again

 

[jasonb75]

Follow my last suggestion and insert a new row at the top for age 100 with the values inflated by 2%

Remember to adjust the formula to include the extra row.

 

[prlondhe]

sir

thank you very much. have another problem with sending email's from excel sheet. Can you provide any help

 

[jasonb75]

I don't know about sending email from excel, see if this helps Mail from Excel and make/mail PDF files (Windows)