GSheets - Find intersection of two named ranges, into a custom formula?

Question

Very simply, I'm working on a dynamic roster for a school.

https://docs.google.com/spreadsheets/d/1fmBsPQDAED4sEhhA-2I35Pi7JTq7_6QJ_jeVfn2w7CI/edit?usp=sharing

'StudentSheet1' | Monday | Tuesday | | -------- | -------- | James | Maths | English |

'TeacherSheet1' | Monday | Tuesday | | ----------------------------- | ------------- | Teacher | =StudentLookup(James, Monday) | |

I want a custom formula, that I can call StudentLookup or just SL. It would refernce the data in the other sheet.

I really appreciate this, been wanting to figure this out for weeks.

I tried naming the column and rows and using the INDEX function, but it would give the whole row, notjust the one cell I wanted in the row. Also it's not a custom formula.

Answer 1

You may try this by creating a named function (as in screenshot):

=ifna(filter(filter(Students!$B:$F,Students!$B$1:$F$1=y),Students!$A:$A=x),"--")