Results 1 to 8 of 8

3D look-ups? Indexes?

Threaded View

  1. #1
    Forum Contributor
    Join Date
    12-02-2008
    Location
    Brisbane
    MS-Off Ver
    2016
    Posts
    811

    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.


    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.
    Last edited by BRISBANEBOB; 12-04-2012 at 11:35 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1