+ Reply to Thread
Results 1 to 5 of 5

Need Rate based on INDEX + MATCH or VLOOKUP formula

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

    Need Rate based on INDEX + MATCH or VLOOKUP formula

    I have shipment data on one sheet and Rate data on next sheet.

    On "Data" Sheet, I need "Audited Rate" in Column Y, based on following matches
    1. Column J - "Shipper City"
    2. Column P - "Consignee City"
    3. Column V - "Billed Wgt"


    3a - We need to match Shipper City (Column J)+ Consignee City (Column P) & have to calculate "Audited Rate" (Column Y)based on "Billed Weight" (Column V)of individual shipment.
    3b - We have weight brackets in next sheet "Rates"
    Based on given weight, we have to find appropriate rate from weight bracket from "Rates" sheet.
    Weight Brackets are as follow on "Rates" Sheet.


    Weight Brackets MIN Value
    1 - 499 LBS 0
    500 - 999 LBS 500
    1,000 - 1,999 LBS 1000
    2,000 - 4,999 LBS 2000
    5,000 - 9,999 LBS 5000
    10,000 - 19,999 LBS 10000
    20,000 + LBS 20000

    MINIMUM CHARGE
    I need "MIN Charge" in Column Z : Based on below matches
    1. Column J - "Shipper City"
    2. Column P - "Consignee City"
    Match both "Shipper City" & "Consignee City" and based on it find the FROM "Rates" sheet under "MINIMUM Charge" Value (Column N)


    EXAMPLE
    Shipper City Consignee City Min Charge
    BURNABY CALGARY 38.68


    Will you please help me to get the "Audited Rate" based on INDEX+MATCH or any kind of VLOOKUP Formula.
    Thanks a lot in advance for all your help.
    Regards,
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by raviraval86; 05-25-2021 at 05:11 PM.

  2. #2
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    3,057

    Re: Need Rate based on INDEX + MATCH or VLOOKUP formula

    Formula for Y3=SUMIFS(INDEX(Rates!$O:$U,,MATCH($V3,Rates!$O$3:$U$3,1)),Rates!$J:$J,$J3,Rates!$M:$M,$P3)

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

    Re: Need Rate based on INDEX + MATCH or VLOOKUP formula

    Thank you JoesphTech.
    That formula worked and really helped.

    After posting it I realized that I needed help with one more thing. "MIN Charge"
    I updated that requirement in post and as well as on the attached file too.

    Will you please help me with MIN Charge formula as well?

    I really do appreciate your help.
    -Ravi

  4. #4
    Forum Expert
    Join Date
    01-05-2013
    Location
    Singapore
    MS-Off Ver
    H&B2016 & H&B2021
    Posts
    3,057

    Re: Need Rate based on INDEX + MATCH or VLOOKUP formula

    Z3=SUMIFS(Rates!N:N,Rates!J:J,J3,Rates!M:M,P3)

  5. #5
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Australia
    MS-Off Ver
    365 V2403 and WPS V2022
    Posts
    3,408

    Re: Need Rate based on INDEX + MATCH or VLOOKUP formula

    worksheet name : Data , Y3 cell , array fomula , drag down
    HTML Code: 
    worksheet name : Data , Z3 cell , array fomula , drag down
    HTML Code: 

+ 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. [SOLVED] Vlookup or Index Match based off a Pivot Table
    By KoolKatelyn in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-05-2018, 08:08 AM
  2. INDEX MATCH or VLOOKUP formula based on a condition
    By apple_tree in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 09-16-2016, 02:31 AM
  3. [SOLVED] VLOOKUP or INDEX Match for Grades based on Team
    By 3345james in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-18-2016, 03:12 PM
  4. Possible INDEX/MATCH FORMULA to get FX Rate in Currency table
    By almugs in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-07-2015, 12:47 AM
  5. Replies: 6
    Last Post: 03-06-2015, 03:20 PM
  6. Replies: 4
    Last Post: 01-13-2014, 04:16 AM
  7. [SOLVED] Conditional formatting based on VLOOKUP/INDEX:MATCH
    By strud in forum Excel General
    Replies: 2
    Last Post: 06-13-2013, 07:02 AM

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