'Higher than and less than' functions in Index

[Arunshanth1985]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[TD]Reference[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Monthly Sales[/TD]
[TD]Rebate Target[/TD]
[TD][/TD]
[TD]Rebate Target[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1245000[/TD]
[TD]1500000[/TD]
[TD][/TD]
[TD]500000[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1000000[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1500000[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2000000[/TD]
[/TR]
</tbody>[/TABLE]

Dear Members Greetings from Nigeria.

First of all Thank you for having this Forum. This really helps me learn a lot.

Please look at the above table. I want to write a formula in cell B2 so when I enter the 'Monthly sales' in A2 excel would automatically pick 'Rebate Target' in cell B2 from reference tables E2 to E5. The 'Rebate target' would be the next highest amount mentioned in rebate scheme (Reference Table) from monthly sales amount.

I am stuck in the place about higher than or less than when using INDEX and MATCH Functions. Please kindly help me write an appropriate formula.

Thank you
Regards
Arun

 

[jproffer]

If it's linear like that (500000 steps) try this:

=ROUNDUP(A1/5,-5)*5