+ Reply to Thread
Results 1 to 6 of 6

Index Match with multiple criteria

  1. #1
    Forum Contributor
    Join Date
    05-02-2017
    Location
    NoVa, United States
    MS-Off Ver
    Microsoft 365
    Posts
    152

    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
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,899

    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




    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 Contributor
    Join Date
    05-02-2017
    Location
    NoVa, United States
    MS-Off Ver
    Microsoft 365
    Posts
    152

    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
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,899

    Re: Index Match with multiple criteria

    You're welcome!!

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

    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
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,899

    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)

Similar Threads

  1. [SOLVED] Index Match with multiple criteria and date criteria
    By snolem75 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-28-2018, 03:51 PM
  2. Replies: 16
    Last Post: 01-05-2018, 11:04 PM
  3. Index and match - multiple possible match criteria
    By JDobbsy1987 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-01-2017, 01:52 PM
  4. Need help in Index, Match usage to match multiple criteria in sum function
    By Summer0830 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-26-2017, 02:47 AM
  5. Index/Match to Match entries on multiple criteria
    By manning457 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-06-2015, 02:32 PM
  6. [SOLVED] Index Match with Multiple Criteria Using Same Criteria Column
    By rominjn in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-05-2015, 11:34 AM
  7. Replies: 2
    Last Post: 09-27-2014, 04:34 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