3D look-ups? Indexes?

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.

Please Login or Register  to view this content.

etc., etc., etc.

Please attach a sample workbook with expected output for getting quick solution.

As long as the STARTING value for each table is in a cell by itself (not text), then this is pretty straightforward. In F5:

=INDEX(OFFSET(INDEX($C$7:$C$10000, MATCH(C5, $C$7:$C$10000, 1)), 2, 1, 9, 9), MATCH(D5, $C$9:$C$17, 0), MATCH(E5, $D$8:$L$8, 0))

OFFSET() take a starting point, then moves x-rows and x-columns to set a new starting point, then is expanded to include y-rows and y-columns to become a reference "range".

=OFFSET(startingrange, moverow(s), movecolumns(x), [height], [width])

It appears you understand INDEX/MATCH/MATCH, and this is all we're really doing, except we create the INDEX range using the OFFSET formula above.

We use MATCH to set the first cell as the first cell in column C that is LESS than the search weight, this keys us to the correct table.
Then the OFFSET function moves down 2 rows, moves over 1 column, then expands to a 9x9 set of cells from that point.


Use the EVALUATE FORMULA function to watch the formula unfold one calc at a time, that should help make it clear.