Hello,
I've been trying to work to get this right for a few days, but to no avail, and would appreciate help.
Here is a preview of the data:
Under each of the Titles (K4:N4), I would like a list of every student (names taken from Column A), who have the corresponding 'Reasons' included in Column F. So under Academic (Column K), I would like all of the students who have had 'Academic' selected as one of the 'Reasons' - not just those who have Academic, but any of those with Academic as one of the options. This return needs to have no blanks and needs to be dynamic (as the student list and reasons will change). I want the lookup to go down to row 100,
I know I will need an array, but I've struggled. I can get the lists to update with no blanks, based on a exact match:
=IFERROR(INDEX($A$1:$A$100,SMALL(IF($F$1:$F$100="Academic",ROW($A$1:$A$100)-ROW($A$1)+1),ROWS($A$1:A1))),"")
But I can't seem to turn the "Academic" into a wildcard so that it searches for partial text so includes all the students who have had Academic selected at all (not just those who have only had Academic selected).
I've also tried:
=IFERROR(INDEX(A3:A101, SMALL(IF(ISNUMBER(SEARCH($M$4,F3:F101)), MATCH(ROW(F3:F101), ROW(F3:F101)), ""), ROWS($A$1:A2))), "")
Which seemed to work, but for some reason only let me display a list of 7 results in the array. Every time I tried to select more than 7, one of them would just drop off the list?!
I feel like I'm so close, and yet so far! Happy to use VBA (already doing it so that the drop-down in F can select multiple), but I think a tweak to one of the arrays should work?
Thanks in advance!
I've been trying to work to get this right for a few days, but to no avail, and would appreciate help.
Here is a preview of the data:
A | B | C | D | E | F | G | H | I | J | K | L | M | N | ||
1 | |||||||||||||||
2 | |||||||||||||||
3 | LISTS | LISTS | LISTS | LISTS | |||||||||||
4 | Name | Key Stage | PP | Gender | Group | Reasons (Drop Down - multiple selectable) | Other | Actions | Academic | Attendance | ATL | Other | |||
5 | Student 1 | 1 | Y | M | 3 | Academic | |||||||||
6 | Student 2 | 4 | Y | F | 6 | Academic, ATL | |||||||||
7 | Student 3 | 5 | N | N/A | 2 | Attendance | |||||||||
8 | Student 4 | 2 | N | M | 4 | Other | |||||||||
9 | Student 5 | 5 | N | M | 1 | Attendance, ATL, Academic | |||||||||
10 | Student 6 | 2 | N | F | 1 | ATL, Other | |||||||||
11 | Student 7 | 5 | Y | M | 5 | Other | |||||||||
12 | Student 8 | 2 | Y | M | 5 | ATL, Attendance | |||||||||
13 | Student 9 | 1 | N | M | 3 | Academic, ATL | |||||||||
14 | Student 10 | 4 | N | M | 6 | ATL, Academic, Attendance |
Under each of the Titles (K4:N4), I would like a list of every student (names taken from Column A), who have the corresponding 'Reasons' included in Column F. So under Academic (Column K), I would like all of the students who have had 'Academic' selected as one of the 'Reasons' - not just those who have Academic, but any of those with Academic as one of the options. This return needs to have no blanks and needs to be dynamic (as the student list and reasons will change). I want the lookup to go down to row 100,
I know I will need an array, but I've struggled. I can get the lists to update with no blanks, based on a exact match:
=IFERROR(INDEX($A$1:$A$100,SMALL(IF($F$1:$F$100="Academic",ROW($A$1:$A$100)-ROW($A$1)+1),ROWS($A$1:A1))),"")
But I can't seem to turn the "Academic" into a wildcard so that it searches for partial text so includes all the students who have had Academic selected at all (not just those who have only had Academic selected).
I've also tried:
=IFERROR(INDEX(A3:A101, SMALL(IF(ISNUMBER(SEARCH($M$4,F3:F101)), MATCH(ROW(F3:F101), ROW(F3:F101)), ""), ROWS($A$1:A2))), "")
Which seemed to work, but for some reason only let me display a list of 7 results in the array. Every time I tried to select more than 7, one of them would just drop off the list?!
I feel like I'm so close, and yet so far! Happy to use VBA (already doing it so that the drop-down in F can select multiple), but I think a tweak to one of the arrays should work?
Thanks in advance!