IF greater than and less than value from corresponding data table

[dophile]

Need Help Urgently!!!!

I have the following:

Miles Service Payable Service <10 <15 <20 >20 10 A A 2 4 6 1.5 8 B B 3 6 9 2.0 16 C C 4 8 12 3.0 22 A D 5 10 15 4.0 18 D E 6 12 18 5.0 <tbody> </tbody>

Need to calculate payable depends on Miles & Services type based on the Miles range. If miles are greater than 20, then additional miles be paid at the 20 miles rate plus miles minus 20 miles * rate. Can someone please help. I tried with index, match formula but when tried with if formula to check the mileage range to pay can not get the right answer. Thank you in advance Don

<colgroup><col span="4"><col><col span="9"></colgroup><tbody>
</tbody>

 

[DRSteele]

Welcome to the forum.

From what I can interpret from what you wrote, I think this might get us moving along. Copy C2 downwards.

	A	B	C	D	E	F	G	H	I
1	Miles	Service	Payable		Service	0	10	15	20
2	10	A	4		A	2	4	6	1.5
3	8	B	3		B	3	6	9	2
4	16	C	12		C	4	8	12	3
5	22	A	1.5		D	5	10	15	4
6	18	D	15		E	6	12	18	5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet14​

Worksheet Formulas Cell Formula C2 =INDEX($F$2:$I$6,MATCH(B2,$E$2:$E$6,0),MATCH(A2,$F$1:$I$1,1)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[dophile]

Thank you DRSteele for your reply.

However the result is not what I am looking for. Maybe I wasn't clear on what I am looking for

For Service A, Miles 22

if Miles are Less than 10 Pay 2, if Less than 15 Pay 4, if less than 20 pay 6 if greater than 20, pay the amount of 20 plus (miles -20) * 1.50 = ((20+ 2*1.5))=23

 

[dophile]

Correction for the last example : if greater than 20 miles the amount payable is $6 + (1.5*2) = $9