How can I sensibly do a 3-d look up? I need to extract shipping rate between two ports and rate is dependent on weight (different weight breaks have different rates as per the tables below). (I can't upload the workbook due to server restrictuions.)
Weight From To Rate Total
8.20 kgs HBT NYC 1.49 12.20
Formula under $1.49 is:
=IF(AND($C$3>B5,$C$3<=C5),INDEX($D$7:$L$15,MATCH(E3,$C$7:$C$15,0),MATCH(F3,$D$6:$L$6,0)),IF(AND(C3>$B$17,C3<=C$17),INDEX($D$19:$L$27,MATCH(E3,$C$19:$C$27,0),MATCH($F$3,$D$18:$L$18,0)),"ETC"))
With this formula you simply run our of nesting levels.
0 5 KGS
LON LAD FRA LAX NYC MIA HBT HRE JNB
LON 2.50 3.20 1.60 2.70 3.40 6.00 1.89 1.27
LAD 2.50 1.45 1.25 2.85 1.98 2.23 2.52 2.33
FRA 3.20 1.45 1.81 1.88 1.40 2.41 1.71 1.24
LAX 1.60 1.25 1.81 1.81 1.88 1.40 2.41 1.71
NYC 2.70 2.85 1.88 1.81 1.67 1.70 2.07 1.99
MIA 3.40 1.98 1.40 1.88 1.67 2.81 1.29 1.85
HBT 6.00 2.23 2.41 1.40 1.70 2.81 1.29 1.52
HRE 1.89 2.52 1.71 2.41 2.07 1.29 1.29 1.93
JNB 1.27 2.33 1.24 1.71 1.99 1.85 1.52 1.93
5.1 10 KGS
LON LAD FRA LAX NYC MIA HBT HRE JNB
LON 2.19 2.80 1.40 2.36 2.98 5.25 1.65 1.11
LAD 2.19 - 1.27 1.09 2.49 1.73 1.95 2.21 2.04
FRA 2.80 1.27 - 1.58 1.65 1.23 2.11 1.50 1.09
LAX 1.40 1.09 1.58 - 1.58 1.65 1.23 2.11 1.50
NYC 2.36 2.49 1.65 1.58 - 1.46 1.49 1.81 1.74
MIA 2.98 1.73 1.23 1.65 1.46 - 2.46 1.13 1.62
HBT 5.25 1.95 2.11 1.23 1.49 2.46 - 1.13 1.33
HRE 1.65 2.21 1.50 2.11 1.81 1.13 1.13 - 1.69
JNB 1.11 2.04 1.09 1.50 1.74 1.62 1.33 1.69 -
10.1 50 KGS
LON LAD FRA LAX NYC MIA HBT HRE JNB
LON 1.88 2.40 1.20 2.03 2.55 4.50 1.42 0.95
LAD 1.88 - 1.09 0.94 2.14 1.49 1.67 1.89 1.75
FRA 2.40 1.09 - 1.36 1.41 1.05 1.81 1.28 0.93
LAX 1.20 0.94 1.36 - 1.36 1.41 1.05 1.81 1.28
NYC 2.03 2.14 1.41 1.36 - 1.25 1.28 1.55 1.49
MIA 2.55 1.49 1.05 1.41 1.25 - 2.11 0.97 1.39
HBT 4.50 1.67 1.81 1.05 1.28 2.11 - 0.97 1.14
HRE 1.42 1.89 1.28 1.81 1.55 0.97 0.97 - 1.45
JNB 0.95 1.75 0.93 1.28 1.49 1.39 1.14 1.45 -
50.1 100 KGS
LON LAD FRA LAX NYC MIA HBT HRE JNB
LON 1.64 2.10 1.05 1.77 2.23 3.94 1.24 0.83
LAD 1.64 - 0.95 0.82 1.87 1.30 1.46 1.65 1.53
FRA 2.10 0.95 - 1.19 1.23 0.92 1.58 1.12 0.81
LAX 1.05 0.82 1.19 - 1.19 1.23 0.92 1.58 1.12
NYC 1.77 1.87 1.23 1.19 - 1.10 1.12 1.36 1.31
MIA 2.23 1.30 0.92 1.23 1.10 - 1.84 0.85 1.21
HBT 3.94 1.46 1.58 0.92 1.12 1.84 - 0.85 1.00
HRE 1.24 1.65 1.12 1.58 1.36 0.85 0.85 - 1.27
JNB 0.83 1.53 0.81 1.12 1.31 1.21 1.00 1.27 -
etc., etc., etc.
Bookmarks