Calculate price for a driver based on distance

[cool2005]

Hi everyone,

Need your help if possible to create a spreadsheet in Excel 2016 that will calculate the price for my driver based on multiple conditions and driven distance:

For example if driver goes for 0-30km will get 10.50 dollars, if it drives between 31-50km will get 18 dollars, if will drive between 51-75KM will get 25 dollars and so on, 76-100 km will get 35 dollars. i was thinking to do it with a formula or make a table first in excel with this values and get the data from there.

basically what i need will be a table that contain name of the driver, driven kilometers and then i will get the price column automatically completed based on specific values:

if driver made 25 km column price will be autofilled with 10.50 dollars
if driver made 71 km will get 25 dollars automatically

i will only insert driven KM and price will be automatically calculated.

 

[S M C]

 

[Dave Patton]

T202209a.xlsm
	D	E	F	G	H	I	J
1						Distance	Rate
2	18	10.50	10.50	10.50		0	10.50
3	35	18.00	18.00	18.00		30.0001	18.00
4	80	25.00	25.00	25.00		75.001	25.00
5	30	10.50	10.50	10.50		100.001	35.00
6	100	25.00	25.00	25.00
7
5b

Cell Formulas
Range	Formula
E2:E6	E2	=LOOKUP(D2,$I$2:$J$5)
F2:F6	F2	=LOOKUP(D2,{0,10.5;30.0001,18;75.001,25;100.001,35})
G2:G6	G2	=VLOOKUP(D2,$I$2:$J$5,2.1)

The above shows two versions of the same formula.
You can paste the post into a clean sheet. Click on the icon below the f(x) in the header and paste to your sheet.
N.B. Note the type of brackets etc.
I also added an example with Vlookup.

 

[Dave Patton]

typo in Vlookup

s/b

T202209a.xlsm
	A	B	C	D	E	F	G	H	I	J
1									Distance	Rate
2				18	10.50	10.50	10.50		0	10.50
3				35	18.00	18.00	18.00		30.0001	18.00
4				80	25.00	25.00	25.00		75.001	25.00
5				30	10.50	10.50	10.50		100.001	35.00
6				100	25.00	25.00	25.00
7
8
5b

Cell Formulas
Range	Formula
E2:E6	E2	=LOOKUP(D2,$I$2:$J$5)
F2:F6	F2	=LOOKUP(D2,{0,10.5;30.0001,18;75.001,25;100.001,35})
G2:G6	G2	=VLOOKUP(D2,$I$2:$J$5,2,1)