+ Reply to Thread
Results 1 to 7 of 7

INDEX MATCH with 2 criteria

  1. #1
    Registered User
    Join Date
    12-17-2020
    Location
    Los Angeles, CA
    MS-Off Ver
    Microsoft 2016
    Posts
    23

    INDEX MATCH with 2 criteria

    Hello friends,

    I have a problem. I will extremely appreciate if someone call help me.

    I need to fill shipping charge taking into consideration 1) zone 2) weight 3) carrier

    I believe it can be done with INDEX and MATCH but here we need to have 3 criteria. Does anyone know how to do it?


    3.JPG

    4.JPG

    Please help me if you know
    Attached Files Attached Files
    Last edited by DIFF1; 04-27-2021 at 05:45 PM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: INDEX MATCH with 2 criteria

    You could make your life easier if you forego the "pretty" layout and put all the tables in line. Then you can do a MATCH against the Courier. You can then use that as an Offset added to a MATCH on the Zone. And the Row MATCH would be a simple one on the Weight.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    12-17-2020
    Location
    Los Angeles, CA
    MS-Off Ver
    Microsoft 2016
    Posts
    23

    Re: INDEX MATCH with 2 criteria

    TMS thank you for your reply. I am not sure what exactly you suggest to do. Could you please (if you can) do it with one table so that i see visually want i need to do. I appreciate your kindness and help.

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: INDEX MATCH with 2 criteria

    INDEX has a little known 4th argument for "Area" Each of your tables would be a different area. This works if all of your tables are set up the same, which they are.
    Holding down the CNTRL Key, select the data portion of each of your tables. Then, in the "Name box" (that white rectangle where you usually see the cell ID) type in what you want to call your data (I called it MyData) and press ENTER. You can now use this formula in G2 copied down
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    I also added the formula for fuel charge in H2
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    See attachment
    Attached Files Attached Files
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  5. #5
    Registered User
    Join Date
    12-17-2020
    Location
    Los Angeles, CA
    MS-Off Ver
    Microsoft 2016
    Posts
    23

    Re: INDEX MATCH with 2 criteria

    ChemistB

    I really appreciate your help. It is extremely helpful.

    I have 1 more question. Do you know how I can calculate the average shipping cost per carrier? Is there some kind of function that can contain both IF and AVG. So that it can look at column C and depending on the carrier average the shipping cost.

    If you know how, please let me know. I really appreciate your kindness and help.

  6. #6
    Registered User
    Join Date
    12-17-2020
    Location
    Los Angeles, CA
    MS-Off Ver
    Microsoft 2016
    Posts
    23

    Re: INDEX MATCH with 2 criteria

    Does anyone know how to calculate shipping cost per carrier. Looking at column C and averaging shipping charge for a specific carrier.

    4q.JPG

    I appreciate your help.
    Attached Files Attached Files

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: INDEX MATCH with 2 criteria


+ 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 fails when the Match Criteria is determined by a formula result
    By JeffGrant in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-22-2021, 12:39 AM
  2. VBA function to match multiple criteria faster than vlookup or index match
    By bkav1991 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 11-09-2020, 09:14 AM
  3. Replies: 1
    Last Post: 03-01-2020, 10:36 PM
  4. [SOLVED] Combining INDEX/MATCH with several criteria and INDEX/SMALL
    By Dresas in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 05-15-2018, 11:02 AM
  5. [SOLVED] Combining 3 Formulas: Return all Names that Match Criteria Using Index/Match
    By bchilme in forum Excel Formulas & Functions
    Replies: 20
    Last Post: 09-29-2014, 09:28 AM
  6. Replies: 2
    Last Post: 09-27-2014, 04:34 PM
  7. Replies: 6
    Last Post: 04-30-2014, 02:42 AM

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