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


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

 

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