I have two Excel sheets open. The first sheet has data organized by Student ID and by School (two different column). In the second Excel sheet, I simply want to return a column that shows the Student ID of all students belonging to a specified school. How do I do this dynamically? If I change the specified school, then presumptively my Student ID list too should change. Thanks in advance - I'm a newbie here!
2
-
1If you have office 365 Excel, look into FILTER(). If not then you will need to use a version of INDEX() with SMALL().– Scott CranerCommented Jul 8, 2021 at 19:29
-
Why not just filter it in place?– gns100Commented Jul 9, 2021 at 2:11
Add a comment
|
2 Answers
Insert a PivotTable of your data. Add "School" field to filters area, and "Student ID" to rows area. Don't add any values. Hey presto, a list of students filtered by school.
Instead of using a filter, consider adding a slicer for the Pivot Table for a more end-user self-service look and feel. No good if list of schools is really long though.
If you want to use Index + Small + IF function, you may refer to following sample.
The following image shows Student ID and and School.
I enter formula =IFERROR(INDEX(Sheet1!A$2:A$12,SMALL(IF($A$2=Sheet1!$B$2:$B$12,ROW($2:$12)-1,4^8),ROW(A1))),"") in cell B2, then drop down.
If I change the School in A2 via Drop-down list, the corresponding student IDs will be queried.
