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!
Bookmarks