+ Reply to Thread
Results 1 to 12 of 12

Index Match with Multiple Criteria

  1. #1
    Registered User
    Join Date
    10-04-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    16

    Index Match with Multiple Criteria

    Hi,

    I need to pull in numbers based on 3 multiple criteria. I have tried 3 variations of a formula, but all result in a value error. See below:

    In my document (attached), I need to pull a number if there are matches between (1) the partner name in the top red box and the column of partner names (2) the software and the row of software, and (3) the "SUM" column for each software.

    Formula 1 =INDEX($F$9:$I$13, MATCH($C7, IF(AND($F$6:$I$6="SUM",$E$9:$E$13=$B$2),$F$8:$I$8),0))

    Formula 2 =INDEX(OFFSET($F$9:$F$13,, MATCH($C8&"SUM",$F$8:$AM$8&$F$6:$AM$6, 0)-1), INDEX(F$9:F$13, MATCH($B$2, $E$9:$E$13, 0))

    Formula 3 =INDEX($F$9:$AM$13,MATCH($C9&"SUM",$F$8:$AM$8&$F6:$AM$6,0))

    Thanks!
    Attached Files Attached Files
    Last edited by celestealexandra; 12-03-2013 at 04:26 PM. Reason: Added updated excel document

  2. #2
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Index Match with Multiple Criteria

    I can't figure out what you want. Like, the MATCH is looking for something that doesnt exist; or you're looking in strange-sized arrays, or you're telling INDEX to get a matrix position but only giving it one coordinate instead of two.

    What are the three criteria you're looking up against anyway? I only see one (the software package), although I guess the person ("Partner n") is the second, and even so, I have no idea what the third is.

  3. #3
    Registered User
    Join Date
    10-04-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    16

    Re: Index Match with Multiple Criteria

    Hi Ben,

    Thanks for reaching out. I have attached a document with color clarifications (the highlighted areas are the criteria). The things that need to be matched are the "SUM", Partner name, and software type.

    Let me know if this helps.

    Thanks!
    Attached Files Attached Files

  4. #4
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Index Match with Multiple Criteria

    Where is SUM listed as a input? I mean, what you want is the "sum" column for each product, rather than the PR15etc stuff, right? Which cell lists that as the input?

    Also your example output values don't match what you're telling me: you've got "Partner 1" listed as the person-under-consideration in C1 and B2:D2 bu then you have the "All Partners" values returned in your "what it should be" column. What does that mean?

  5. #5
    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 Multiple Criteria

    Perhaps if you included a few sample answers, it would help us understand what you want?
    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

  6. #6
    Registered User
    Join Date
    10-04-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    16

    Re: Index Match with Multiple Criteria

    Hi FDibbins,

    In the original post, I have attached an updated document with sample answers and color codes to show matched areas.

    Thanks,
    C

  7. #7
    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 Multiple Criteria

    OK based on your sample answers, try this, copied down...
    =INDEX($G$8:$AN$20,MATCH($B$2,$F$8:$F$20,0),MATCH($C7,$G$8:$AN$8,0))

  8. #8
    Registered User
    Join Date
    10-04-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    16

    Re: Index Match with Multiple Criteria

    Actually, I it works!

    Could you explain why this works without including the "Sum" in the index match? I need to replicate this "3 criteria" match on several different documents with different structures.

    Thanks,
    C

  9. #9
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,841

    Re: Index Match with Multiple Criteria

    I think the reason that the MATCH formula of FDibbins works without including "SUM" is that MATCH will find the FIRST cell that matches, searching from left to right. The first cell in this case is always in the SUM column.
    I included the SUM in my formula since I figured you may need any of the other columns as well. However, that would require a yet more advanced formula because of the 15a, 15b etc.

    Note that this is an array formula, needs to be entered with Ctrl + Shift + Enter.

    edit: changed "right to left" to " left to right"
    Attached Files Attached Files
    Last edited by Jacc; 12-05-2013 at 11:45 AM.
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  10. #10
    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 Multiple Criteria

    Jacc is correct. I am basing that formula on you needing to find a value in the 1st of the 3 columns (because that's what your sample showed). If you need to find values in the other, was can modify that to do that, too. It would probably require an IF() statement that adds 1 or 2 to the 2nd MATCH()

  11. #11
    Registered User
    Join Date
    10-04-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    16

    Re: Index Match with Multiple Criteria

    Hi Jacc,

    Thank you so much! I was actually able to replicate it for another section that was similar. However, I was unable to edit it for still more criteria. If you have another second to help, I have attached the more difficult version.

    In the attached, I have a list of codes and their frequency for various partners. I need to pull the top four codes and their corresponding counts and denominators. I have already used a RANK formula to determine which are the top codes for each partner (I want 1-4). Between the two worksheets, I need to match (1) Partner name (i.e., A B C D) (2) Question number (i.e., PO1, PO2) (3) Rank Number 1-4. I keep get #N/A or #VALUE, even when inputting as an array.

    You are a saint, thanks again for all your help!

    C
    Attached Files Attached Files

  12. #12
    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 Multiple Criteria

    Try this for a start...
    =INDEX(Qual_Post!$A$1:$V$31,MATCH('Report Generator'!$B$2,Qual_Post!$A$1:$A$31,0),MATCH('Report Generator'!B$3,Qual_Post!$A$1:$V$1,0))

+ 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 Multiple Criteria
    By ertweety in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-07-2013, 11:42 AM
  2. [SOLVED] How to use Index and Match with multiple criteria?
    By PistachioPedro in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-02-2013, 02:33 PM
  3. match or index multiple criteria
    By simpson in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-16-2013, 02:13 PM
  4. Index and match with multiple criteria
    By felixpanganiban in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-04-2012, 01:06 PM
  5. Index match with multiple criteria
    By Kristina1976 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-11-2010, 12:16 PM

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