Need to calculate the amount payable for Columns A & B matching columns E...H

[dophile]

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 miles 20 plus (miles -20) * 1.50 = ((6+ 2*1.5))=9

 

[vletm]

How urgently?
How 10A would give Payable 2? Miles is over 10 and <= 15 ... hmm?
This is one 'sample' of answer ...
... and it would be nicer, if Your Sample File would be Excel-file.

 

[dophile]

Thank you vletm for the reply. I have attached an Excel file. It's urgent and I really mean it. I need to compare these rates to the prevailing rates by tomorrow.

Thank you.

 

[Nebu]

Hi:

=IFERROR(IF(A3>20,INDEX($F$3:$I$6,MATCH(B3,$E$3:$E$6,0),MATCH(LOOKUP(A3,$F$2:$H$2,$F$2:$H$2),$F$2:$H$2,0))+(A3-20)*INDEX($I$3:$I$6,MATCH(B3,$E$3:$E$6)),INDEX($F$3:$I$6,MATCH(B3,$E$3:$E$6,0),MATCH(LOOKUP(A3,$F$2:$H$2,$F$2:$H$2),$F$2:$H$2,0))),"")

Is this what you are looking for?

Thanks

 

[bosco_yip]

Another shorter possible………

In C3 copy down :

=LOOKUP(A3,F$2:H$2,INDEX(F$3:H$6,MATCH(B3,E$3:E$6,0),0))+(A3>20)*INDEX(I$3:I$6,MATCH(B3,E$3:E$6,0))*(A3-20)

Regards

 

[dophile]

Another shorter possible………

In C3 copy down :

=LOOKUP(A3,F$2:H$2,INDEX(F$3:H$6,MATCH(B3,E$3:E$6,0),0))+(A3>20)*INDEX(I$3:I$6,MATCH(B3,E$3:E$6,0))*(A3-20)

Regards

Perfect...Thank you so much bosco_yip. Really appreciated it very much.

 

[dophile]

Hi:

=IFERROR(IF(A3>20,INDEX($F$3:$I$6,MATCH(B3,$E$3:$E$6,0),MATCH(LOOKUP(A3,$F$2:$H$2,$F$2:$H$2),$F$2:$H$2,0))+(A3-20)*INDEX($I$3:$I$6,MATCH(B3,$E$3:$E$6)),INDEX($F$3:$I$6,MATCH(B3,$E$3:$E$6,0),MATCH(LOOKUP(A3,$F$2:$H$2,$F$2:$H$2),$F$2:$H$2,0))),"")

Is this what you are looking for?

Thanks

Thank you Nebu, I will try to incorporate your formula and check it later, as I have found bosco_yip's formula shorter and working for me. Appreciate your response.

 

[Nebu]

Hi:

Bosco's response is the same logic I have used , it just that it is bit shorter

Thanks

 

[dophile]

Hi:

Bosco's response is the same logic I have used , it just that it is bit shorter

Thanks

Thank you once again Nebu, Looking forward for your help in any future stumbles like this.