Getting Specific Rates for Certain Cell Conditions (Logistics)

[harveygpintys]

Hello All,

Firstly, I'm glad to be a member on this community and excited to have my first post.

Here's my query: So I wanted column F to auto populate the CWT rate for our shipments. This column F would at least show how much cwt the carrier "column E" would charge us for that weight "column D" going to destination city "Column B".

***CWT rate is different for each Carriers. It's like a carrier's quote for our company per 100 pounds of shipment to different locations.

I have a separate table that have all these CWT Data for all our carriers:

Your help is very much appreciated! Thank you in advance

 

[DRSteele]

Welcome to the forum.

Could you please upload that sample data so that we can conduct experiments? To do so download and install the add-in the xl2bb Tool.

 

[harveygpintys]

Welcome to the forum.

Could you please upload that sample data so that we can conduct experiments? To do so download and install the add-in the xl2bb Tool.

Hi DRSteele, please see below:

CWT Rate is based on the Carrier, and rates differ according to weight (lbs) and city.

Rates Table - Harvey Updating.xlsx
	A	B	C	D	E	F
1	Delivery	City	PROV	Weight (LBS)	Carrier	CWT Rate
2	80369132	Varennes	QC	1360.869	ERB
3	80368077	AIRDRIE	AB	13313.716	TRX
Sheet4

Cell Formulas
Range	Formula
B2:B3	B2	=IFERROR(VLOOKUP([Delivery],Table2[#All],23,),"-")
C2:C3	C2	=IFERROR(VLOOKUP([Delivery],Table2[#All],24,),"-")
D2:D3	D2	=IFERROR(VLOOKUP([Delivery],Table2[#All],47,),"-")
E2:E3	E2	=IFERROR(VLOOKUP([@Delivery],Table2[#All],21,),"-")

Here's the Rates Table I base my CWT rate for carriers "TRX" and "ERB"

Rates Table - Harvey Updating.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Carrier	Service	Origin City	Destination City	Province	Area	Minimum	LTLs	500 LBs	1,000 LBs	1,500 LBs	2,000 LBs2	3,000 LBs	5,000 LBs	10,000 LBs	20,000 LBs	30,000 LBs
48	Erb	Road	Mississauga	Varennes	QC	Quebec	$ 59.80	$ 15.95	$ 15.95	$ 10.86	$ 10.86	$ 9.29	$ 9.29	$ 5.58	$ 5.25	$ 3.74	$ -
105	TRX	Road	Mississauga	AIRDRIE	AB	West	$ 126.84	$ 42.77	$ 36.79	$ 29.82	$ 29.82	$ 26.82	$ 26.82	$ 25.54	$ 24.68	$ 21.16	$ 15.00
Rates Data

Please let me know if you need anything else. I'd love to learn from this experience as well.

 

[DRSteele]

Hi. Sorry I missed your response. I'll try to help out tomorrow.

 

[harveygpintys]

Hi. Sorry I missed your response. I'll try to help out tomorrow.

Thank you so much! This is also a learning experience for me.