Help! I have searched and tried multiple functions but cannot get the one I'm looking for. I am planning an elementary field day with 25 homerooms. Each homeroom has 18 students. Field day consists of 7 different events. The students in a homeroom are assigned to 4 of the 7 events, which means that 6 of the events will have 10 kids participating, and 1 event will have 12 kids participating. I went to a round robin generator to figure out which students should be randomly assigned to which events. The numbers in the Events table below correlates to the student number in the Student table. How can I get a formula so that it will populate the Events table based on the student's name that corresponds to the respective number? I want to be able to enter in each homeroom student list and let it fill in the Events table to print for the teacher. I have tried VLookup but I cannot get it to work on the entire table.
| Number | Student |
| 1 | Student A |
| 2 | Student B |
| 3 | Student C |
| 4 | Student D |
| 5 | Student E |
| 6 | Student F |
| 7 | Student G |
| 8 | Student H |
| 9 | Student I |
| 10 | Student J |
| 11 | Student K |
| 12 | Student L |
| 13 | Student M |
| 14 | Student N |
| 15 | Student O |
| 16 | Student P |
| 17 | Student Q |
| 18 | Student R |
| Event 1 | Event 2 | Event 3 | Event 4 | Event 5 | Event 6 | Event 7 |
| 9 | 15 | 14 | 11 | 8 | 8 | 12 |
| 10 | 12 | 15 | 9 | 13 | 11 | 6 |
| 18 | 13 | 5 | 16 | 17 | 9 | 10 |
| 11 | 7 | 8 | 6 | 6 | 16 | 5 |
| 1 | 3 | 1 | 4 | 15 | 3 | 14 |
| 8 | 16 | 13 | 12 | 10 | 12 | 7 |
| 4 | 9 | 18 | 17 | 5 | 4 | 3 |
| 5 | 17 | 2 | 7 | 1 | 2 | 18 |
| 2 | 6 | 10 | 2 | 14 | 7 | 13 |
| 14 | 11 | 4 | 3 | 18 | 15 | 17 |
| | | | | | | 16 |
| | | | | | | 1 |
