+ Reply to Thread
Results 1 to 8 of 8

Index Match to Nth Value - Think I'm nearly there but its not working

  1. #1
    Registered User
    Join Date
    07-19-2019
    Location
    London, England
    MS-Off Ver
    MS Office 2016
    Posts
    9

    Index Match to Nth Value - Think I'm nearly there but its not working

    In the attached spreadsheet I am preparing a Course for a non-profit organisation. I am using data in the spreadsheet for mailmerges and also scheduling. Students have assignments during the course and the second sheet ('PSS Assignments' with named ranges) contains the individual names and assignments.

    The 'Roster' sheet has the personnel data and I want to show next to each persons name all of their assignments. I have got a working formula for the first assignment in column 'J' using match/index. However, I think I now need to use an array formula in column K to find the 2nd assignment (3rd assignment in column L and so on). This is a first time for me and its not working!

    So far I have got to this - please see cell K2

    =INDEX(Assignments,SMALL(IF(Lead_Participant=$G$2,ROW(Lead_Participant)-ROW(INDEX(Lead_Participant,1,1))+1),$J$2))

    When I understand what I am doing wrong I am sure I can use the correct formula in columns L to Q.


    Any help would be much appreciated!
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    47,765

    Re: Index Match to Nth Value - Think I'm nearly there but its not working

    1st thing I notice is that, while you have a full array for INDEX, you are not telling excel which column to use - ie you need a 2nd MATCH for ID the column for you

    If you only need info from col A, then change the INDEX range to just be A or add 1 at the end...

    =INDEX(Assignments,SMALL(IF(Lead_Participant=$G$2,ROW(Lead_Participant)-ROW(INDEX(Lead_Participant,1,1))+1),$J$2),1)
    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

  3. #3
    Registered User
    Join Date
    07-19-2019
    Location
    London, England
    MS-Off Ver
    MS Office 2016
    Posts
    9

    Re: Index Match to Nth Value - Think I'm nearly there but its not working

    Thank you FDibbins.

    I tried doing this but maybe I'm missing something. When I copy it down I get the following

    Capture.JPG

    So it works for 'Kurtis Santos' (returning the 2nd instance '32'), but not for 'Rima Cumbo' which should appear ('31'). I manipulated the data in the sample spreadsheet (see attached revised .xlsx) to check if it would work for other instances, but it returns an error...really confusing me! Can you help me understand why this might be?

    Thanks
    Attached Files Attached Files
    Last edited by Nomades; 08-07-2019 at 04:45 AM.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    47,765

    Re: Index Match to Nth Value - Think I'm nearly there but its not working

    what exactly are you trying to do here?
    What is the J2 at the end doing?

  5. #5
    Registered User
    Join Date
    07-19-2019
    Location
    London, England
    MS-Off Ver
    MS Office 2016
    Posts
    9

    Re: Index Match to Nth Value - Think I'm nearly there but its not working

    In the "Roster sheet" I want to see all the individual assignments (columns J to R) a person has in a row with their names (column G).

    To do that I am trying to write a formula in "Roster" sheet column K to give the number of a 2nd assignment - if one exists - that is recorded in the 'PSS Assignments' sheet in column A. To get this number I need the formula to match the name from "Roster" column G with the name in "PSS Assignment" column B and then look one cell left for the Assignment number (titled 'Assign #') in column A.
    Ditto 'Roster' column L to show the 3rd assignment of the person (whose name is in 'Roster' column G) from PSS Assignments column A.
    Ditto 'Roster' column M a 4th assignment number of the person...

    I have got the first assignment - the formula is in column J - using MATCH INDEX but obviously this won't work for a 2nd, 3rd, 4th, etc

    So, in the "Roster" sheet with the test data, for "Cumbo, Rima"
    cell J2 should show '1' (the person first appears in cell "PSS Assignments!B1", one cell left gives the number '1')
    cell K2 should show '31' (2nd occurrence is in cell "PSS Assignments!B32", one cell left gives the number '31')
    cell L2 should be blank (I plan to use an error formula if there is no result as per J2)
    cell M2 ditto blank

    End 'J2'. My understanding is that to get the next value in the sequence (Nth) you need to tell it what the previous value in the sequence was. So looking for the 2nd value in the sequence I tell it the first one / 'nth 1' can be found in cell 'J2'. I would finish the formula for the 3rd in the sequence (in cell 'L2') by providing the 2nd value, found in 'K2', etc.
    Am I missing something with this?

    Thanks for your help...sorry if my confusion is now adding to your confusion!
    Last edited by Nomades; 08-08-2019 at 05:15 PM.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    30,365

    Re: Index Match to Nth Value - Think I'm nearly there but its not working

    The last argument in a SMALL or LARGE function is just the number of the Nth item, not its location. So for the smallest, you need 1, for the second smallest 2 and so on.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  7. #7
    Registered User
    Join Date
    07-19-2019
    Location
    London, England
    MS-Off Ver
    MS Office 2016
    Posts
    9

    Re: Index Match to Nth Value - Think I'm nearly there but its not working


    When you come to London next, can I buy you a drink?

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    30,365

    Re: Index Match to Nth Value - Think I'm nearly there but its not working

    Non-alcoholic spritzer of some description for me, please.

    If this has helped to resolve the issue, please mark the thread as solved.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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