Calculations using Postcode

[ianharper68]

Following on from my thread Shipping calculations for weight based sheet

My customer has now asked me to calculate rates based on weight of goods from specific postcodes.

Please see below, if somebody could help I would highly appreciate it

Sample Calcs.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2
3
4		Description	Collection Postcode	Collie	Length	Width	Height	LDM	CBM	NW	GW	ADR	Collection Fee	Collection Costs
5		Tools	7844 NZ NL	1	120	100	105	1.26	0.43	125.00	156.00	No	Note 1	Note 2
6
7
8		Note 1 - if ADR = NO	= Collection Fee (C10-C31)
9
10		Note 1 - if ADR = YES	= Collection Fee (C40-C61)
11
12		Note 2 - if ADR = NO	This must equal - Collection postcode then GW (K3) * Cost per weight (c10 - h34)
13			as the above example would 156kg from 7844 NZ NL would €127
14
15		Note 2 - if ADR = YES	This must equal - Collection postcode then GW (K3) * Cost per weight (c40 - h61)
16			as the above example would 156kg from 7844 NZ NL would €184.15
17
18
19			Cost per collection by weight (NON ADR)
20		Collection Postcode	Collection Fee	900	1750	3000	3500	4500	5000
21		2380 AT	€ 50.00	€ 543.00	€ 608.00	€ 627.00	€ 713.00	€ 833.00	€ 1,305.00
22		2610 BE	€ 50.00	€ 182.00	€ 244.00	€ 258.00	€ 304.00	€ 355.00	€ 490.00
23		1032 CH	€ 50.00	€ 398.00	€ 465.00	€ 480.00	€ 529.00	€ 575.00	€ 837.00
24		22047 DE	€ 50.00	€ 329.00	€ 370.00	€ 384.00	€ 413.00	€ 440.00	€ 579.00
25		22934 DE	€ 50.00	€ 329.00	€ 370.00	€ 384.00	€ 413.00	€ 440.00	€ 579.00
26		2650 DK	€ 50.00	€ 338.00	€ 428.00	€ 488.00	€ 603.00	€ 693.00	€ 898.00
27		9401 DK	€ 50.00	€ 347.00	€ 422.00	€ 498.00	€ 629.00	€ 712.00	€ 939.00
28		34410 FR	€ 50.00	€ 349.00	€ 473.00	€ 514.00	€ 573.00	€ 632.00	€ 822.00
29		44482 FR	€ 50.00	€ 302.00	€ 324.00	€ 332.00	€ 377.00	€ 420.00	€ 595.00
30		45801 FR	€ 50.00	€ 255.00	€ 270.00	€ 275.00	€ 304.00	€ 333.00	€ 424.00
31		6728 HU	€ 50.00	€ 600.00	€ 703.00	€ 815.00	€ 930.00	€ 1,024.00	€ 1,582.00
32		20094 IT	€ 50.00	€ 385.00	€ 502.00	€ 578.00	€ 742.00	€ 810.00	€ 1,210.00
33		45030 IT	€ 50.00	€ 438.00	€ 580.00	€ 668.00	€ 857.00	€ 982.00	€ 1,429.00
34		2215 TC NL	€ 50.00	€ 117.00	€ 163.00	€ 180.00	€ 218.00	€ 237.00	€ 314.00
35		4758 NL	€ 50.00	€ 117.00	€ 163.00	€ 180.00	€ 218.00	€ 237.00	€ 314.00
36		6716 AE NL	€ 50.00	€ 122.00	€ 173.00	€ 183.00	€ 219.00	€ 238.00	€ 359.00
37		7844 NZ NL	€ 50.00	€ 127.00	€ 174.00	€ 200.00	€ 233.00	€ 265.00	€ 383.00
38		8262 CG NL	€ 50.00	€ 124.00	€ 173.00	€ 194.00	€ 229.00	€ 255.00	€ 372.00
39		81-574 PL	€ 50.00	€ 397.00	€ 499.00	€ 548.00	€ 669.00	€ 732.00	€ 1,107.00
40		83-000 PL	€ 50.00	€ 397.00	€ 499.00	€ 548.00	€ 669.00	€ 732.00	€ 1,107.00
41		85-862 PL	€ 50.00	€ 369.00	€ 478.00	€ 514.00	€ 634.00	€ 702.00	€ 1,017.00
42		08970 SP	€ 50.00	€ 342.00	€ 525.00	€ 602.00	€ 669.00	€ 834.00	€ 1,355.00
43
44			Cost per collection by weight (ADR)
45		Collection Postcode	Collection Fee	900	1750	3000	3500	4500	5000
46		2380 AT	€ 75.00	€ 787.35	€ 881.60	€ 909.15	€ 1,033.85	€ 1,207.85	€ 1,892.25
47		2610 BE	€ 75.00	€ 263.90	€ 353.80	€ 374.10	€ 440.80	€ 514.75	€ 710.50
48		1032 CH	€ 75.00	€ 577.10	€ 674.25	€ 696.00	€ 767.05	€ 833.75	€ 1,213.65
49		22047 DE	€ 75.00	€ 477.05	€ 536.50	€ 556.80	€ 598.85	€ 638.00	€ 839.55
50		22934 DE	€ 75.00	€ 477.05	€ 536.50	€ 556.80	€ 598.85	€ 638.00	€ 839.55
51		2650 DK	€ 75.00	€ 490.10	€ 620.60	€ 707.60	€ 874.35	€ 1,004.85	€ 1,302.10
52		9401 DK	€ 75.00	€ 503.15	€ 611.90	€ 722.10	€ 912.05	€ 1,032.40	€ 1,361.55
53		34410 FR	€ 75.00	€ 506.05	€ 685.85	€ 745.30	€ 830.85	€ 916.40	€ 1,191.90
54		44482 FR	€ 75.00	€ 437.90	€ 469.80	€ 481.40	€ 546.65	€ 609.00	€ 862.75
55		45801 FR	€ 75.00	€ 369.75	€ 391.50	€ 398.75	€ 440.80	€ 482.85	€ 614.80
56		6728 HU	€ 75.00	€ 870.00	€ 1,019.35	€ 1,181.75	€ 1,348.50	€ 1,484.80	€ 2,293.90
57		20094 IT	€ 75.00	€ 558.25	€ 727.90	€ 838.10	€ 1,075.90	€ 1,174.50	€ 1,754.50
58		45030 IT	€ 75.00	€ 635.10	€ 841.00	€ 968.60	€ 1,242.65	€ 1,423.90	€ 2,072.05
59		2215 TC NL	€ 75.00	€ 169.65	€ 236.35	€ 261.00	€ 316.10	€ 343.65	€ 455.30
60		4758 NL	€ 75.00	€ 169.65	€ 236.35	€ 261.00	€ 316.10	€ 343.65	€ 455.30
61		6716 AE NL	€ 75.00	€ 176.90	€ 250.85	€ 265.35	€ 317.55	€ 345.10	€ 520.55
62		7844 NZ NL	€ 75.00	€ 184.15	€ 252.30	€ 290.00	€ 337.85	€ 384.25	€ 555.35
63		8262 CG NL	€ 75.00	€ 179.80	€ 250.85	€ 281.30	€ 332.05	€ 369.75	€ 539.40
64		81-574 PL	€ 75.00	€ 575.65	€ 723.55	€ 794.60	€ 970.05	€ 1,061.40	€ 1,605.15
65		83-000 PL	€ 75.00	€ 575.65	€ 723.55	€ 794.60	€ 970.05	€ 1,061.40	€ 1,605.15
66		85-862 PL	€ 75.00	€ 535.05	€ 693.10	€ 745.30	€ 919.30	€ 1,017.90	€ 1,474.65
67		08970 SP	€ 75.00	€ 495.90	€ 761.25	€ 872.90	€ 970.05	€ 1,209.30	€ 1,964.75
Sheet1

Cell Formulas
Range	Formula
H5	H5	=E5*F5*G5/1000000
I5	I5	=(F5*G5/2.45)/10000
D46:I67	D46	=D21*0.45+D21

Cells with Data Validation
Cell	Allow	Criteria
C5	List	=$B$21:$B$42

 

[Kaper]

N5:

=IF(L5="No",INDEX(D21:I42,MATCH(C5,B21:B42,0),XMATCH(K5,D20:I20,1)),INDEX(D46:I67,MATCH(C5,B46:B67,0),XMATCH(K5,D45:I45,1)))

M5:

=VLOOKUP(C5,IF(L5="No",B21:C42,B46:C67),2,0)

 

[ianharper68]

N5:

=IF(L5="No",INDEX(D21:I42,MATCH(C5,B21:B42,0),XMATCH(K5,D20:I20,1)),INDEX(D46:I67,MATCH(C5,B46:B67,0),XMATCH(K5,D45:I45,1)))

M5:

=VLOOKUP(C5,IF(L5="No",B21:C42,B46:C67),2,0)

Amazing Thank you works perfect

 

[Kaper]

Glad to hear that. And thanks for marking answer as solution.