+ Reply to Thread
Results 1 to 11 of 11

Index Match With 2 Sets of Criteria, Each with Multiple Conditions

  1. #1
    Registered User
    Join Date
    10-02-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    6

    Index Match With 2 Sets of Criteria, Each with Multiple Conditions

    Hi,

    I'm having trouble wrapping my head around a seemingly simple ask, and can't get any iteration of my index match formulas with multiple criteria to work.

    See attached workbook for more details, but basically what I need my formula to do is first evaluate the component type in each cell (component types are "BRE", "Form", "Insert", and "OE"), and then within each component type, return a different % of acceptable overs for each component type, depending on the range of quantity ordered. (i.e. if Component Type = "BRE", and Quantity is b/t 0 and 10,000, return a value of 5%, if Component Type = "BRE", and Quantity is b/t 10,001 and 25,000 return a value of 3%, and so on.

    Any help is greatly appreciated, and if there's a similar thread that already exists on the forum I apologize, but I was unable to find anything and am at my wit's end on this one.

    Thanks very much,
    Kevin
    Attached Files Attached Files
    Last edited by MrMacro2013; 06-23-2014 at 06:16 PM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Index Match With 2 Sets of Criteria, Each with Multiple Conditions

    Hi there. Try this one out. it looks OK to me.

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




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Index Match With 2 Sets of Criteria, Each with Multiple Conditions

    d2
    Please Login or Register  to view this content.
    and drag down.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Index Match With 2 Sets of Criteria, Each with Multiple Conditions

    Hi and welcome to the forum

    Try this, copied down...
    =INDEX($H$2:$K$7,MATCH(C2,$F$2:$F$7,1),MATCH(B2,$H$1:$K$1,0))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Registered User
    Join Date
    10-02-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Index Match With 2 Sets of Criteria, Each with Multiple Conditions

    Thanks so much Glenn & Oeldere! Both solutions worked perfectly! You're both my heroes! I wish I had posted sooner instead of banging my head against the wall for an hour. Thanks again gusy.

  6. #6
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Index Match With 2 Sets of Criteria, Each with Multiple Conditions

    Thanks for the reply.

    Glad I could help.

  7. #7
    Registered User
    Join Date
    10-02-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Index Match With 2 Sets of Criteria, Each with Multiple Conditions

    Thanks FDibbins! One question though - is there any way to tweak your formula so that the quantities in Column F can be within a given range? (i.e. if C2 is b/ F2 & G20 rather than matching the exact value in C2)?

  8. #8
    Registered User
    Join Date
    10-02-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Index Match With 2 Sets of Criteria, Each with Multiple Conditions

    *G2 no G20 sorry

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Index Match With 2 Sets of Criteria, Each with Multiple Conditions

    That formula is looking for anything less than the value in column F, it is not looking for an exact match, as do both of the other 2 formulas given

    Try it by changing a value in C

  10. #10
    Registered User
    Join Date
    10-02-2013
    Location
    US
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Index Match With 2 Sets of Criteria, Each with Multiple Conditions

    Ahhh touche good sir, sorry to be a bother. Thanks to all of you for your help on this, I truly appreciate it!

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Index Match With 2 Sets of Criteria, Each with Multiple Conditions

    No bother at all, happy to help and thanks for the feedback

+ 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. Index Match Rows for multiple data sets
    By Martin Chamberlin in forum Excel General
    Replies: 1
    Last Post: 05-23-2014, 07:07 AM
  2. Replies: 13
    Last Post: 12-13-2012, 11:44 AM
  3. INDEX/MATCH with Multiple Conditions
    By Lost123 in forum Excel General
    Replies: 4
    Last Post: 03-26-2010, 01:06 PM
  4. Index data that match multiple conditions
    By razaas in forum Excel General
    Replies: 10
    Last Post: 12-30-2009, 04:23 AM
  5. Index + Multiple Match Conditions??
    By Spellbound in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-01-2007, 04:07 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