Getting Specific Rates for Certain Cell Conditions (Logistics)

harveygpintys

New Member
Joined
Jun 16, 2020
Messages
3
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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.

1592337015296.png


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

1592337337380.png


Your help is very much appreciated! Thank you in advance :)
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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.
 
Upvote 0
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
ABCDEF
1DeliveryCity PROVWeight (LBS)CarrierCWT Rate
280369132VarennesQC1360.869ERB
380368077AIRDRIEAB13313.716TRX
Sheet4
Cell Formulas
RangeFormula
B2:B3B2=IFERROR(VLOOKUP([Delivery],Table2[#All],23,),"-")
C2:C3C2=IFERROR(VLOOKUP([Delivery],Table2[#All],24,),"-")
D2:D3D2=IFERROR(VLOOKUP([Delivery],Table2[#All],47,),"-")
E2:E3E2=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
ABCDEFGHIJKLMNOPQ
1CarrierServiceOrigin CityDestination CityProvince 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
48ErbRoadMississaugaVarennesQCQuebec $ 59.80 $ 15.95 $ 15.95 $ 10.86 $ 10.86 $ 9.29 $ 9.29 $ 5.58 $ 5.25 $ 3.74 $ -
105TRXRoadMississaugaAIRDRIEABWest $ 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. :)
 
Upvote 0
Hi. Sorry I missed your response. I'll try to help out tomorrow.
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top