+ Reply to Thread
Results 1 to 4 of 4

Index & Match Formula to Get Value

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

    Index & Match Formula to Get Value

    Hi,
    I have two separate tabs on one file.
    1. Weight
    2. rates

    I have below matching columns on them.
    Carrier
    Weight
    Density
    Rate
    Type
    Origin City
    Origin Prov
    Destination City
    Destination Prov

    On "Weights" tab, based on those matching field, I need to know the matching rate between those FROM & TO city from "RATES" sheet based on the matching weight.


    For all the shipments on "weight" sheet, I have individual weight of those shipments.
    Where as in "rates" sheet, I have rate for specific weight brackets.

    WEIGHT BREAK
    0-499 500 -999 1000-1999 2000-4999 5000-9999 10000-19999 20000-29999 >30000

    So, any load that has weight between 0 & 499 they have same rate and for shipments weighing between 500 to 999 lbs the weight rate is same & so.

    Can you please help me to get one formula which I can use in Column "K" , "Freight Rate" based on rates from sheet "rates"

    Thanks a lot in advance for your help with same.

    Kindly find the file attached herewith for reference.
    -Ravi
    Attached Files Attached Files

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,584

    Re: Index & Match Formula to Get Value

    Here are 2 options, the first will only work if you have the FILTER function available.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Note, both will produce errors but that's down to the data not the formulas.

    For example, on the Weight sheet you have St-Constant in the destination column and on the Rates sheet you have St. Constant.
    If posting code please use code tags, see here.

  3. #3
    Valued Forum Contributor wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Malaysia
    MS-Off Ver
    2007/MSO365
    Posts
    881

    Re: Index & Match Formula to Get Value

    worksheet weight K2 array formula

    HTML Code: 
    =INDEX(Rates!$H$4:$O$1462,MATCH(B2&D2&G2&I2,Rates!$A$4:$A$1462&Rates!$B$4:$B$1462&Rates!$D$4:$D$1462&Rates!$F$4:$F$1462,),MATCH(E2+1%,{0,500,1000,2000,5000,10000,20000,29999.999}))
    Add Origin Prov , formula as below
    HTML Code: 
    =INDEX(Rates!$H$4:$O$1462,MATCH(B2&D2&G2&H2&I2,Rates!$A$4:$A$1462&Rates!$B$4:$B$1462&Rates!$D$4:$D$1462&Rates!$E$4:$E$1462&Rates!$F$4:$F$1462,),MATCH(E2+1%,{0,500,1000,2000,5000,10000,20000,29999.999}))
    Last edited by wk9128; 09-17-2020 at 02:31 AM.

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    6,256

    Re: Index & Match Formula to Get Value

    In K2 then copied down
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

+ 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] Index Match not cycling through entire index. Formula not updating when values change...
    By nordxnortheast in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-07-2020, 10:44 AM
  2. Replies: 1
    Last Post: 08-17-2019, 01:11 PM
  3. Index Match formula changing my Index daily
    By vitt4300 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-31-2017, 02:19 PM
  4. [SOLVED] This formula works but I donīt understand why Index(Index) match
    By campelliann in forum Excel General
    Replies: 2
    Last Post: 01-25-2016, 05:55 PM
  5. Replies: 3
    Last Post: 05-02-2013, 01:31 AM
  6. Index Match Index Formula work slow
    By avk in forum Excel General
    Replies: 9
    Last Post: 03-07-2012, 02:19 PM
  7. Replies: 5
    Last Post: 02-29-2012, 08:51 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