Hi all,
Currently I have two files. ( I have put the data of both on separate tabs)
1. Weights
2. Rates
Based on value from file "rates", I need to find the rate of load on Column "J" on "weights" file.
Currently, I have below parameters based on which I have to filter the data for both Weights and rates to find the value in Column J "Freight Rate".
1. Column B : "Carrier"
2. Column C : "Organisation"
3. Column D : "Weight Density"
4. Column E : "Weight"
5. Column G : "Origin City"
6. Column H : "Destination City"
Based on above information, I have to look for the freight rate in "Rates" file.
"Rates" file has below information based on which I have to filter the data to match with "weight" file on Column J.
1. Column A : "Carrier"
2. Column B : "Density"
3. Column D : "Origin City"
4. Column H : "Destination City"
The rate has been bifurcated based on the range, ( 0-499, 500-999, 1000-1999, 2000-4999, 5000-9999, 10000-19999, 20000-29999, 30000+)
As I am not a pro at Excel, Currently, I am breaking the actual file of "weights" and "rates" multiple times to have specific rate by filtering the data in smaller files by carrier, weight density & origin city.
I have attached the solution as well in sheet "Overland-Finning 8 Lbs Weights" based on "Overland-Finning 8 Lbs Rates" file.
After I slice the files by carrier and weight density, I am able to run the below formula to get the "freight Rates" in column J.
=VLOOKUP(I2,'Overland - Finning 8 Lbs Rates'!$F$4:$O$35,MATCH('OverlandFinning 8 Lbs Weights'!E2,'Overland - Finning 8 Lbs Rates'!$H$3:$O$3,1)+2,FALSE)
But my Excel skills are limited and I need help to run a formula, based on which I don't have to slice the actual "weights" and "rates" file multiple times. (Currently I have to slice the weight and rates file more then 20 times by running mentioned, carrier, density, &origin city and it is taking too much time and inefficient.)
Can you please help me to run a single formula so that I don't have to slice the files multiple times and I can get the result in column "J" on "weights" file?
Your help is greatly appreciated.
I have attached the files for reference tables.
Bookmarks