Return lookup/index(match) list based on partial text - with no blanks

[dslhs]

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:

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!

 

[RasGhul]

I converted range A4:H15 into an excel Table1, this will make the Reasons lists dynamic as you add Students.

Drag the array across and down to return all possible students.

Book2
	A	B	C	D	E	F	G	H	I		K	L	M	N
1
2
3											LISTS	LISTS	LISTS	LISTS
4	Name	Key Stage	PP	Gender	Group	Reasons	Other	Actions			Academic	Attendance	ATL	Other
5						(Drop Down - multiple selectable)
6	Student 1	1	Y	M	3	Academic					Student 1	Student 3	Student 2	Student 4
7	Student 2	4	Y	F	6	Academic, ATL					Student 2	Student 5	Student 5	Student 6
8	Student 3	5	N	N/A	2	Attendance					Student 5	Student 8	Student 6	Student 7
9	Student 4	2	N	M	4	Other					Student 9	Student 10	Student 8
10	Student 5	5	N	M	1	Attendance, ATL, Academic					Student 10		Student 9
11	Student 6	2	N	F	1	ATL, Other							Student 10
12	Student 7	5	Y	M	5	Other
13	Student 8	2	Y	M	5	ATL, Attendance
14	Student 9	1	N	M	3	Academic, ATL
15	Student 10	4	N	M	6	ATL, Academic, Attendance
Sheet1

Cell Formulas
Range	Formula
K6:N15	K6	=IFERROR(INDEX(Table1[Name],SMALL(IF(ISNUMBER(SEARCH(K$4,Table1[Reasons])),ROW(Table1[Name])-ROW(Table1[[#Headers],[Name]])),ROWS($K$6:K6))),"")

 

[dslhs]

Thanks. That works brilliantly.

Is there a way of doing it without turning it into a table? It's not a deal breaker, but I would prefer if I didn't have to? I imagine I'd have to just put $A$4:$A$100 for Table1[Name] and $F$4:$F$100 for Table1[Reasons], but not sure what I would do for ROW(Table1[[#Headers],[Name]?

Many thanks,

 

[Fluff]

How about

+Fluff 1.xlsm
	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	Other	Actions			Academic	Attendance	ATL	Other
5						(Drop Down - multiple selectable)
6	Student 1	1	Y	M	3	Academic					Student 1	Student 3	Student 2	Student 4
7	Student 2	4	Y	F	6	Academic, ATL					Student 2	Student 5	Student 5	Student 6
8	Student 3	5	N	N/A	2	Attendance					Student 5	Student 8	Student 6	Student 7
9	Student 4	2	N	M	4	Other					Student 9	Student 10	Student 8
10	Student 5	5	N	M	1	Attendance, ATL, Academic					Student 10		Student 9
11	Student 6	2	N	F	1	ATL, Other							Student 10
12	Student 7	5	Y	M	5	Other
13	Student 8	2	Y	M	5	ATL, Attendance
14	Student 9	1	N	M	3	Academic, ATL
15	Student 10	4	N	M	6	ATL, Academic, Attendance
16
17
Main

Cell Formulas
Range	Formula
K6:N13	K6	=IFERROR(INDEX($A$6:$A$100,AGGREGATE(15,6,(ROW($A$6:$A$100)-ROW($A$6)+1)/(ISNUMBER(SEARCH(K$4,$F$6:$F$100))),ROWS(K$6:K6))),"")

 

[dslhs]

Brilliant. Thank you both so much. You've saved me hours of stress!

 

[Fluff]

Glad we could help & thanks for the feedback.