Calculating cost based on distance and weight

[abztaffyboy]

Afternoon all
Newbie here so please be gentle with me

I have a query in regards to the attached file. Im looking to have a spreadsheet where if i type in a mileage and weight of a piece of freight being collected it will look at the attached table and instantly place the information for me into a given cell

for example if i have a piece of freight to pick up 405 km away and it weighs 125kg i would want the system to look at over 400km but less than 500km and over 100kg but less than 200kg thuus giving back 236 (E9)

im stumped.

Can somebody please help. sorry for posting a pic here but the system says the file is too large





0-50KG 51-100KG 101-200KG 201-300KG 301-500KG 501-1000KG 1001-5000KG 1*20' container 1*40' container
25KM 35 35 35 57 67 87 164 164 164
50KM 35 35 35 57 67 87 164 164 164
100KM 70 92 102 134 136 219 250 250 250
200KM 100 122 216 288 369 530 557 300 300
300KM 100 122 216 288 369 530 557 350 350
400KM 130 147 226 298 379 540 567 467 467
500KM 130 147 236 308 389 550 578 467 467
700KM 130 147 246 318 399 560 588 671 671
800KM 130 158 256 329 429 570 599 819 819
900KM 130 158 266 339 439 580 609 860 860
1000KM 130 182 291 371 479 627 658 903 903
1100KM 130 192 301 381 489 637 669 946 946
1200KM 130 202 311 391 499 647 679 996 996

 

[eibi]

Welcome to the forums!

See attached file -- I propose a formula like this:

=INDEX(B2:H14,MATCH(B17,A2:A14,1),MATCH(B18,B1:H1,1))

Where your data is in B2:H14

Your distance thresholds are in A2:A14

Your weight thresholds are in B1:H1

Your distance input is in B17, and your weight input is in B18