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

1. ## 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

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!

2. ## 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...

3. ## 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

4. ## 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. ## 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?

6. ## 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.

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

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