Forum Discussion
looking up across sheets?
You may use INDEX/MATCH https://www.excel-easy.com/examples/index-match.html to return lessons and their numbers based on teacher code and class number. If you provide sample file it'll be easier to illustrate on formula.
Thanks for your reply, I was thinking maybe Index and Match would do it, but I need to learn how to use them better.
My concern is if one teacher is teaching 2 year 7 Maths classes, how do i get both classes listed against their name? How do I do a formula that puts all of their classes across the row against their name?
I've attached the file for you to have a look at.
Thanks again for looking at this for me, much appreciated!
Thank you for the file. Could you please clarify how do you map 2021 classes on Class 1, Class 2, etc in Sheet1? Perhaps you may add one-two codes to the tables with manually added desired result.
Another question is on which version of Excel you are? Mainly is it support dynamic arrays or not, other words do you have functions as XLOOKUP, FILTER.
So what we normally do is manually enter, on "2021 Classes' sheet, the teacher code against a class, depending on who we want to teach it.
Then I copy all of those codes into one list (so instead of being multiple lists across the sheet (eg Year 7 classes, then Year 8 classes etc) they are all in one big list). I then sort them by the teacher code and copy across to sheet 1 and paste down the bottom somewhere, then transpose them, 1 teacher at a time, so they become horizontal lists for each teacher which I then copy and paste up the top of sheet 1 against the teacher code. This is just for the teacher code and class code. I also use vlookup to get the hours with the codes as well.
Very messy, hence my need for a better solution!
I'll repost the file with some example teacher codes on '2021 classes' and the desired effect on Sheet 1.
I have Excel for Office 365:
Thanks again for helping me!
I have