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

dslhs

New Member
Joined
Apr 4, 2022
Messages
48
Office Version
  1. 2019
Platform
  1. Windows
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:


ABCDEFGHIJKLMN
1
2
3LISTSLISTSLISTSLISTS
4NameKey StagePPGenderGroupReasons
(Drop Down - multiple selectable)
OtherActionsAcademicAttendanceATLOther
5Student 11YM3Academic
6Student 24YF6Academic, ATL
7Student 35NN/A2Attendance
8Student 42NM4Other
9Student 55NM1Attendance, ATL, Academic
10Student 62NF1ATL, Other
11Student 75YM5Other
12Student 82YM5ATL, Attendance
13Student 91NM3Academic, ATL
14Student 104NM6ATL, 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!
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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
ABCDEFGHIKLMN
1
2
3LISTSLISTSLISTSLISTS
4NameKey StagePPGenderGroupReasonsOtherActionsAcademicAttendanceATLOther
5(Drop Down - multiple selectable)
6Student 11YM3AcademicStudent 1Student 3Student 2Student 4
7Student 24YF6Academic, ATLStudent 2Student 5Student 5Student 6
8Student 35NN/A2AttendanceStudent 5Student 8Student 6Student 7
9Student 42NM4OtherStudent 9Student 10Student 8 
10Student 55NM1Attendance, ATL, AcademicStudent 10 Student 9 
11Student 62NF1ATL, Other  Student 10 
12Student 75YM5Other    
13Student 82YM5ATL, Attendance    
14Student 91NM3Academic, ATL    
15Student 104NM6ATL, Academic, Attendance    
Sheet1
Cell Formulas
RangeFormula
K6:N15K6=IFERROR(INDEX(Table1[Name],SMALL(IF(ISNUMBER(SEARCH(K$4,Table1[Reasons])),ROW(Table1[Name])-ROW(Table1[[#Headers],[Name]])),ROWS($K$6:K6))),"")
 
Upvote 0
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,
 
Upvote 0
How about
+Fluff 1.xlsm
ABCDEFGHIJKLMN
1
2
3LISTSLISTSLISTSLISTS
4NameKey StagePPGenderGroupReasonsOtherActionsAcademicAttendanceATLOther
5(Drop Down - multiple selectable)
6Student 11YM3AcademicStudent 1Student 3Student 2Student 4
7Student 24YF6Academic, ATLStudent 2Student 5Student 5Student 6
8Student 35NN/A2AttendanceStudent 5Student 8Student 6Student 7
9Student 42NM4OtherStudent 9Student 10Student 8 
10Student 55NM1Attendance, ATL, AcademicStudent 10 Student 9 
11Student 62NF1ATL, Other  Student 10 
12Student 75YM5Other    
13Student 82YM5ATL, Attendance    
14Student 91NM3Academic, ATL
15Student 104NM6ATL, Academic, Attendance
16
17
Main
Cell Formulas
RangeFormula
K6:N13K6=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))),"")
 
Upvote 0
Brilliant. Thank you both so much. You've saved me hours of stress!
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,958
Messages
6,175,633
Members
452,661
Latest member
Nonhle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top