+ Reply to Thread
Results 1 to 4 of 4

Match two criteria and return multiple records - INDEX & AGGREGATE, TEXTJOIN function

  1. #1
    Registered User
    Join Date
    11-02-2016
    Location
    Bellingham
    MS-Off Ver
    2010
    Posts
    23

    Match two criteria and return multiple records - INDEX & AGGREGATE, TEXTJOIN function

    Hello Everybody,

    This forum has been very useful in the past and I got rusty in Excel formula.

    I work for an After school and I am trying to make our roster more efficient.

    I have a list of students, the school they attend and a weekly schedule with a "X" for the day they are signed in.
    I am trying to organize the pickup and depending on the school they are going they will be picked by a different person.
    What I would like to do is, when I wrote the school name, the kids that have a X on the Monday row will be showing.

    To complicate the problem, my employer work on a MAC and this will need to be converted to Number app in the long way.
    Attached is an example file.
    Thank you in advance for your help.
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,013

    Re: Match two criteria and return multiple records - INDEX & AGGREGATE, TEXTJOIN function

    For Excel

    K5 drag down
    =IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW($C$4:$C$14)/(INDEX($D$4:$H$14,,MATCH($K$3,$D$3:$H$3,))="x")/($C$4:$C$14=$K$4),ROWS(K$5:K5))),"")

    or press Ctrl+Shift+Enter and drag down
    =IFERROR(INDEX(B:B,SMALL(IF((INDEX($D$4:$I$14,,MATCH($K$3,$D$3:$H$3,))="x")*($C$4:$C$14=$K$4),ROW($C$4:$C$14)),ROWS(K$5:K5))),"")

    I never use MAC.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    11-02-2016
    Location
    Bellingham
    MS-Off Ver
    2010
    Posts
    23

    Re: Match two criteria and return multiple records - INDEX & AGGREGATE, TEXTJOIN function

    It work perfectly, Thank you so much,
    It also help me realized that the formula were not working because my excel is in another languages.
    the file you attached translated it.
    Thank you again.

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    6,013

    Re: Match two criteria and return multiple records - INDEX & AGGREGATE, TEXTJOIN function

    Happy to help

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. Replies: 10
    Last Post: 08-20-2018, 11:43 AM
  2. Index match with multiple criteria (text not return)
    By balajisrinath87 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 10-31-2017, 07:18 AM
  3. [SOLVED] Index/Match with Multiple Criteria and Multiple return values
    By Brawnystaff in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-28-2015, 10:27 AM
  4. SOS:Return multiple values against multi criteria match and index function
    By nitesh_inin in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-26-2012, 05:45 PM
  5. Replies: 3
    Last Post: 06-15-2012, 04:19 PM
  6. [SOLVED] Return a value:index/match against multiple criteria on another sheet
    By Southfish in forum Excel General
    Replies: 3
    Last Post: 04-30-2012, 11:08 PM
  7. [SOLVED] INDEX-MATCH function return '0' if search criteria not found
    By Ben.SFM in forum Excel General
    Replies: 2
    Last Post: 04-27-2012, 01:46 PM

Tags for this Thread

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