+ Reply to Thread
Results 1 to 5 of 5

Need to use VLOOKUP with Match function based on Multiple criteria

  1. #1
    Registered User
    Join Date
    10-08-2019
    Location
    Calgary, Canada
    MS-Off Ver
    MS 365
    Posts
    13

    Need to use VLOOKUP with Match function based on Multiple criteria

    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.
    Attached Files Attached Files
    Last edited by raviraval86; 04-13-2020 at 10:21 AM. Reason: Spelling miatake

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Need to use VLOOKUP with Match function based on Multiple criteria

    Hi,

    On your weights sheet you have in D "LBS" but in Rates column B you have "Lb"

    LBS <> LB so your search fails on this column. Then look at SumProduct to do matches to find the correct Row in the Rates tab.

    Also there is a space in "8 LBS" and no space in "8Lb" that keeps the match from happening...
    Last edited by MarvinP; 04-13-2020 at 01:36 AM.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Need to use VLOOKUP with Match function based on Multiple criteria

    Hi raviraval,

    I have a formula for you after making the 8 LBS the same as 8lb and also moving the 0-3000 scale to the left one column. The monster looks like this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Find the first few yellow cells filled in on the Weight sheet. Check them to insure they are correct. Oh also I put in an Index column so I could return the correct row that matched to the right of the rates table.
    Hope this helps solve this problem.
    Weights and Rates 8lb not 8 LBS.xlsx

  4. #4
    Registered User
    Join Date
    10-08-2019
    Location
    Calgary, Canada
    MS-Off Ver
    MS 365
    Posts
    13

    Re: Need to use VLOOKUP with Match function based on Multiple criteria

    Hi MarvinP, I checked the file again and realized that there was typo on the file. both "weights" & "rate" file should have "weight Desnsity" values as LBS only.

    If we keep both values Can you please help me to understand the formula with SUMPRODUCT?

  5. #5
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Need to use VLOOKUP with Match function based on Multiple criteria

    Hi ravi,

    After about an hour building the above formula that didn't seem to work because "8 LBS" didn't match "8lb" I fixed the second sheet so they would match exactly. Then the SumProduct() part of the formula almost worked. I needed to know which row the matching information was on. I created a column to the right of your data that returned that row. After I had the row that matched your requirements, I used a HLookup() formula with a TRUE argument to see which column I needed to read. I needed to move the top scale left a few to make the Offset return the correct column.

    The formula above is one of the longer ones I've needed to do in a week or two. You need to study three topics. SumProduct(), HLookup() and Offset. I really can't make you understand the formula without you learning and understand the 3 smaller parts. I search the net with Bing instead of Google, so "Bing It!"

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 03-01-2020, 10:36 PM
  2. Replies: 1
    Last Post: 01-18-2019, 03:55 PM
  3. [SOLVED] How to use Index and Match based on multiple criteria using multiple worksheets
    By MariMano in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-15-2017, 09:01 AM
  4. Need help in Index, Match usage to match multiple criteria in sum function
    By Summer0830 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-26-2017, 02:47 AM
  5. MATCH Function - Multiple Criteria
    By Stressed_Daniel in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-22-2015, 11:20 PM
  6. Replies: 11
    Last Post: 06-01-2015, 02:21 AM
  7. [SOLVED] Find last match in vllokup
    By snax500 in forum Excel General
    Replies: 1
    Last Post: 05-04-2005, 05:06 PM

Tags for this Thread

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