I have a table with a list of teachers' names in a column, and the columns that follow it are individual training courses. The idea is for each course we'd have one "Lead Tutor" and three other "Tutors", and these labels are allocated against selected teachers' names. So it looks something like:
| Teacher | training1 | training2 | training3 |
|---|---|---|---|
| john | Lead tutor | ||
| bob | Tutor | Tutor | Tutor |
| jane | Tutor | Tutor | |
| alice | Tutor | Lead tutor | |
| tim | Tutor | Tutor | Tutor |
| gus | Lead tutor |
What I am trying to create a separate summary table to show who has been assigned to each course, something like:
| Lead tutor | Tutor | Tutor | Tutor | |
|---|---|---|---|---|
| training1 | john | bob | alice | tim |
| training2 | alice | bob | jane | tim |
| training3 | gus | bob | jane | tim |
I know I need something like index/match/small/if/column/row but of all the examples I've found online I just can't get it to work...
The closest I got it to work is:
{=IFERROR(INDEX($A$2:$A$14,SMALL(IF(G$2=$B$2:$C$14,ROW($B$2:$C$14)-1,"not allocated"),COLUMNS($F$3:F3))),"")}
It doesn't give me error messages but it also didn't give me the correct results...!
I've uploaded my attempt on GoogleDrive - would be immensely grateful if someone could shed some light on what I did wrong (I've only learnt about arrays and index/aggregate/match on youtube but just couldn't get my head round it...!)
https://drive.google.com/file/d/1YKhKppAevNGrU_XFGe3IVNFOXynoKFiS/view?usp=sharing
Thanks in advance
I worked on a single formula to use.
In G3 use the following: =LET(x,INDEX(Table24,,MATCH($F3,$1:$1,0)),IFERROR(INDEX(Table24[[name]:[name]],SMALL(IF(G$2=x,ROW(x)-1,""),COUNTIF($G$2:G$2,G$2))),""))
Or if you have an older version of Excel use this (avoiding LET function):
=IFERROR(INDEX(Table24[[name]:[name]],SMALL(IF(G$2=INDEX(Table24,,MATCH($F3,$1:$1,0)),ROW(INDEX(Table24,,MATCH($F3,$1:$1,0)))-1,""),COUNTIF($G$2:G$2,G$2))),"")