Looking up multiple values where one value is in a range band on a matrix

[jm0102]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Weight From
[/TD]
[TD]Weight To
[/TD]
[TD]Weight Category
[/TD]
[TD]Route 1
[/TD]
[TD]Route 2
[/TD]
[TD]Route 3
[/TD]
[TD]Route 4
[/TD]
[TD]Route 5
[/TD]
[/TR]
[TR]
[TD]0
[/TD]
[TD]30
[/TD]
[TD]1
[/TD]
[TD]43
[/TD]
[TD]20
[/TD]
[TD]35
[/TD]
[TD]28
[/TD]
[TD]41
[/TD]
[/TR]
[TR]
[TD]30.01
[/TD]
[TD]50
[/TD]
[TD]2
[/TD]
[TD]43
[/TD]
[TD]20
[/TD]
[TD]35
[/TD]
[TD]28
[/TD]
[TD]41
[/TD]
[/TR]
[TR]
[TD]50.01
[/TD]
[TD]75
[/TD]
[TD]3
[/TD]
[TD]52
[/TD]
[TD]25
[/TD]
[TD]40
[/TD]
[TD]35
[/TD]
[TD]49
[/TD]
[/TR]
[TR]
[TD]75.01
[/TD]
[TD]100
[/TD]
[TD]4
[/TD]
[TD]55
[/TD]
[TD]29
[/TD]
[TD]43
[/TD]
[TD]39
[/TD]
[TD]53
[/TD]
[/TR]
</tbody>[/TABLE]

Hi there,

I’m trying to work out a formula to help me extract a cost which is dependent on a certain shipping route and a weight range.
EG: I have multiple weight ranges in a vertical column on a table (above) which are for example – 1) 1-30 lbs, 2) 30.01-50 lbs, 3) 50.01-75 lbs, etc.

On the rows along the top the headings are simply 1-5 which are each of the routes I’m using.

The values in the table or matrix are the costs that are applicable to the weight range and route criteria.

So if I have a shipment which is 29lbs and needs to go on route 3, I need a formula to return in another sheet what the cost would be. The bit I’m stuck on is how to make the formula look at the problem and return the correct cost for that weight which falls into the 1-30lb range and for the correct route. What I'd like to do (example below) is return the weight category in column 4 using one formula and then use that result to work out the cost in column 5.

I could use a huge lookup table in the background to return a weight category but as I am looking at shipment weights of anything up to 25,000 lbs that would involve a large lookup and I want to keep my file as small as possible. I'm not sure if an IF formula is the best solution for this?

I hope I’ve explained this well enough but I’m happy to clarify anything and have added in a visual example of the costing matrix above.

The report I'm tying to produce will look like:

Weight Transit Lane Route Weight Category Shipment Cost
29lbs London - New York 3 1 35


Please can someone help as I'm stuck

 

[yesterdays]

[TABLE="class: grid, width: 960"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD="colspan: 1"]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD="align: right"]J[/TD]
[TD="align: right"]K[/TD]
[TD="align: right"]L[/TD]
[TD="align: right"]M[/TD]
[TD="align: right"]N[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Weight[/TD]
[TD]transiland[/TD]
[TD]route[/TD]
[TD]Weight category[/TD]
[TD="colspan: 1"]shipmentcost[/TD]
[TD][/TD]
[TD] Weight From[/TD]
[TD]Weight To[/TD]
[TD]Weight Category[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]29[/TD]
[TD]London - New York[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]35[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]41[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]30.01[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]41[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]50.01[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]49[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]75.01[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]53[/TD]
[/TR]
</tbody>[/TABLE]


D2: =INDEX($J$2:$J$5,MATCH(A2,$H$2:$H$5,1))
E2: =INDEX($K$2:$O$5,D2,C2)

 

[jm0102]

[TABLE="class: grid, width: 960"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD="align: right"]J[/TD]
[TD="align: right"]K[/TD]
[TD="align: right"]L[/TD]
[TD="align: right"]M[/TD]
[TD="align: right"]N[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Weight[/TD]
[TD]transiland[/TD]
[TD]route[/TD]
[TD]Weight category[/TD]
[TD]shipmentcost[/TD]
[TD][/TD]
[TD] Weight From[/TD]
[TD]Weight To[/TD]
[TD]Weight Category[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]29[/TD]
[TD]London - New York[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]35[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]41[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]30.01[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]41[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]50.01[/TD]
[TD="align: right"]75[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]49[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]75.01[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]39[/TD]
[TD="align: right"]53[/TD]
[/TR]
</tbody>[/TABLE]


D2: =INDEX($J$2:$J$5,MATCH(A2,$H$2:$H$5,1))
E2: =INDEX($K$2:$O$5,D2,C2)

Many, many thanks - problem solved!