+ Reply to Thread
Results 1 to 6 of 6

Index Match with multiple criteria

  1. #1
    Registered User
    Join Date
    05-02-2017
    Location
    NoVa, United States
    MS-Off Ver
    2010
    Posts
    96

    Index Match with multiple criteria

    Okay I've been searching on how to do this and I'm just not getting it correct. This webpage seems to be doing what I want to do. However my formula isn't coming out right.

    Basically I have to sheets. One contains active people and the other contains every ID those people have. So the other sheet would have multiple rows of the active people each containing a different ID for them. I'm only interested in two forms of those ID's. The one's starting with "A" and the ones starting with "B".

    Some people do not have any ID and others have an A and B ID and maybe a C or D ID as well. The formula I'm using is:
    {=IFERROR(INDEX(Sheet2!H:H,MATCH(1,(A3=Sheet2!A:A)*(Sheet2!H:H="%A%"),0)),"")}

    And it's just not working.

    On the test sheet, the formula is:
    {=IFERROR(INDEX(Sheet2!B:B,MATCH(1,(A2=Sheet2!A:A)*(Sheet2!B:B="%A%"),0)),"")}

    Where am I going wrong?
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    21,558

    Re: Index Match with multiple criteria

    It might have been better to give us your (manually calculated) exppected answers rather than a non-working formula. They rarely point us in the right direction:

    =IFERROR(INDEX(Sheet2!$B:$B,AGGREGATE(15,6,ROW(Sheet2!$B$2:$B$100)/((Sheet2!$A$2:$A$140=Sheet1!$A2)*(LEFT(Sheet2!$B$2:$B$140,1)={"A","B"})),COLUMNS($B:B))),"")

    see sheet.
    Attached Files Attached Files
    Glenn



  3. #3
    Registered User
    Join Date
    05-02-2017
    Location
    NoVa, United States
    MS-Off Ver
    2010
    Posts
    96

    Re: Index Match with multiple criteria

    Thanks - I was way off. I will keep that in mind for the future.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    21,558

    Re: Index Match with multiple criteria

    You're welcome!!

  5. #5
    Forum Contributor
    Join Date
    04-26-2015
    Location
    Toronto, Canada
    MS-Off Ver
    2010
    Posts
    379

    Re: Index Match with multiple criteria

    Quote Originally Posted by Glenn Kennedy View Post

    =IFERROR(INDEX(Sheet2!$B:$B,AGGREGATE(15,6,ROW(Sheet2!$B$2:$B$100)/((Sheet2!$A$2:$A$140=Sheet1!$A2)*(LEFT(Sheet2!$B$2:$B$140,1)={"A","B"})),COLUMNS($B:B))),"")
    Just a FMI question Glenn, I normally use the INDEX SMALL ROW INDEX formula for this, how do you extend your formula, which as it stands in the example posted collects data from two columns, across 3 or more columns?
    Uploading an attachment will assist Forum Members in finding the correct Solution for you. Create a dummy worksheet if necessary, add your start data and show where / what your expected results should be. TO ADD A FILE:

    Go to 'Advanced. click on 'Manage Attachments. Select 'Browse & Choose your file, Select 'Upload and then close the Attachment window.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    21,558

    Re: Index Match with multiple criteria

    I'm not quite sure what you mean. Can you mock up a sample and post in a new thread? PM me to alert me to the fact that you have posted and I'll take a look asap thereafter.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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