Index or Match Function

[dcaraher]

I have attached a workbook which contains a data sheet and a form sheet.

I am creating a form which calculates whether it is better to pay an employee for their business mileage using the HMRC approved rates or at the actual negotiated fuel rate we get as a business.

The form asks the employee to enter their vehicle cc rating and choose a fuel type. They then enter their business mileage and their vehicle MPG.

Calculating the rate paid using the negotiated fuel rate is easy cell B11 on the form sheet does this.

Calculating the rate paid using the HMRC AFR is more difficult. I need to take the fuel type and the cc rating of the vehicle and apply the relevant pence per mile from the table in the data sheet and multiply this by the business miles.

I think this may be achieved using Index but I cant work out a solution.

Any help would be appreciated.

[dcaraher]

I have managed to solve this myself see new attached file for solution.

[martindwilson]

for petrol
=LOOKUP(Form!B3,{0,1,1401,2001},{0,0.13,0.15,0.21}) but your index/,match is both quicker and shorter!