Combine IF for text with INDEX and MATCH? Or VLOOKUP? QUERY?

Question

Coming back to the best source I know to ask for help with something I'm trying to do for our little school. I have one sheet where I enter the date and the name of the student under what they're supposed to do for dismissal (car rider, bus, or aftercare). But teachers are finding it too difficult to go through many students at one to find their own, so I'd like them to each have their own tab with only their students. So a function should pull the name of the student from "Changes," match it to the "Student Reference" tab, then put it on the tab of the teacher in charge of that student.

I can just barely manage INDEX and MATCH alone, and conditions alone, but can't even start to figure out how to go about what I want to do, even though surely it must be possible.

Thank you soooo much in advance for any help! This site has taught me so much.

Answer 1

Try this formula in Cell_A2 of JANE tab. Change the teacher name within the formula while applying it to the other tabs:

=reduce(torow(,1),sequence(1,3,1,2),lambda(a,c,ifna(hstack(a,ifna(filter(choosecols(Changes!A:F,c,c+1),xlookup(choosecols(Changes!A:F,c+1),'Student Reference'!D:D,'Student Reference'!C:C,)="JANE"),wraprows(,2,))))))