Students Schedule on Excel 2016

[knaimi]

My excel sheets has the schedule of all the students in my school. The schedule has the following fields. Please see the sample for one student below:

A

B

C

D

E

G

H

I

J

K

L

M

N

O

P

1

Counselor

Grade

Ident

Student Name

Period

Course Title

Teacher

Room

Ident

Student Name

Period

Course Title

Teacher

Room

2

counselor 1

10

ID #

Student 1

1

Class 1

Teacher 1

Room 1

ID #

Student 6

2

3

counselor 1

10

ID #

Student 1

2

Class 2

Teacher 2

Room 2

ID #

Student 890

2

4

counselor 1

10

ID #

Student 1

3

Class 3

Teacher 3

Room 3

ID #

Student 908

2

5

counselor 1

10

ID #

Student 1

4

Class 4

Teacher 4

Room 4

ID #

Student 1000

2

6

counselor 1

10

ID #

Student 1

5

Class 5

Teacher 5

Room 5

ID #

Student 1001

2

7

counselor 1

10

ID #

Student 1

6

Class 6

Teacher 6

Room 6

8

counselor 1

10

ID #

Student 1

7

Class 7

Teacher 7

Room 7

<tbody>
</tbody>

Each student has 7 lines that represent 7 different classes that he/she is currently enrolled in. In our school we have 1400 students * 7 lines which is equals to 9800 lines of information that is located between column A to I. In columns K to M, I need to add a list of individual students. I need to place a formula to find what class, teacher, and room the student will be in based on a period that I will add in column M. I just want to point out that each student has a unique ID number. The info in columns N, O, & P should change if I change the period # in column M. I hope you can help me find a formula to help with this task.
Thank you,

 

[Scott T]

I did not add all the data. Adjust ranges to match your data. Copy the formula in L2 across and down.

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Counselor	Grade	Ident	Student Name	Period	Course Title	Teacher	Room	Ident	Student Name	Period	Course Title	Teacher	Room
2	counselor 1	10	ID #	Student 1	1	Class 1	Teacher 1	Room 1	ID #	Student 6	2	Class 2six	6Teacher 2	Room 26
3	counselor 1	10	ID #	Student 1	2	Class 2	Teacher 2	Room 2	ID #	Student 890	7	Class 7890	Teacher 7	Room 78
4	counselor 1	10	ID #	Student 1	3	Class 3	Teacher 3	Room 3	ID #	Student 908	2
5	counselor 1	10	ID #	Student 1	4	Class 4	Teacher 4	Room 4	ID #	Student 1000	2
6	counselor 1	10	ID #	Student 1	5	Class 5	Teacher 5	Room 5	ID #	Student 1001	2
7	counselor 1	10	ID #	Student 1	6	Class 6	Teacher 6	Room 6
8	counselor 1	10	ID #	Student 1	7	Class 7	Teacher 7	Room 7
9	counselor 1	11	ID #	Student 6	1	Class 1six	6Teacher 1	Room 16
10	counselor 1	11	ID #	Student 6	2	Class 2six	6Teacher 2	Room 26
11	counselor 1	11	ID #	Student 6	3	Class 3six	6Teacher 3	Room 36
12	counselor 1	11	ID #	Student 6	4	Class 4sx	6Teacher 4	Room 46
13	counselor 1	11	ID #	Student 6	5	Class 5six	6Teacher 5	Room 56
14	counselor 1	11	ID #	Student 6	6	Class 6six	6Teacher 6	Room 66
15	counselor 1	11	ID #	Student 6	7	Class 7six	6Teacher 7	Room 76
16	counselor 2	11	ID #	Student 890	1	Class 1890	Teacher 11	Room 18
17	counselor 2	11	ID #	Student 890	2	Class 2890	Teacher 21	Room 28
18	counselor 2	11	ID #	Student 890	3	Class 3890	Teacher 31	Room 38
19	counselor 2	11	ID #	Student 890	4	Class 4890	Teacher 41	Room 48
20	counselor 2	11	ID #	Student 890	5	Class 5890	Teacher 51	Room 58
21	counselor 2	11	ID #	Student 890	6	Class 6890	Teacher 61	Room 68
22	counselor 2	11	ID #	Student 890	7	Class 7890	Teacher 7	Room 78
Sheet1

Cell Formulas
Range	Formula
L2	{=INDEX(F$2:F$22,MATCH($J2&$K2,$D$2:$D$22&$E$2:$E$22,0))}
L3	{=INDEX(F$2:F$22,MATCH($J3&$K3,$D$2:$D$22&$E$2:$E$22,0))}
M2	{=INDEX(G$2:G$22,MATCH($J2&$K2,$D$2:$D$22&$E$2:$E$22,0))}
M3	{=INDEX(G$2:G$22,MATCH($J3&$K3,$D$2:$D$22&$E$2:$E$22,0))}
N2	{=INDEX(H$2:H$22,MATCH($J2&$K2,$D$2:$D$22&$E$2:$E$22,0))}
N3	{=INDEX(H$2:H$22,MATCH($J3&$K3,$D$2:$D$22&$E$2:$E$22,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[knaimi]

I did not add all the data. Adjust ranges to match your data. Copy the formula in L2 across and down.

Excel 2010

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Counselor	Grade	Ident	Student Name	Period	Course Title	Teacher	Room	Ident	Student Name	Period	Course Title	Teacher	Room
2	counselor 1	10	ID #	Student 1	1	Class 1	Teacher 1	Room 1	ID #	Student 6	2	Class 2six	6Teacher 2	Room 26
3	counselor 1	10	ID #	Student 1	2	Class 2	Teacher 2	Room 2	ID #	Student 890	7	Class 7890	Teacher 7	Room 78
4	counselor 1	10	ID #	Student 1	3	Class 3	Teacher 3	Room 3	ID #	Student 908	2
5	counselor 1	10	ID #	Student 1	4	Class 4	Teacher 4	Room 4	ID #	Student 1000	2
6	counselor 1	10	ID #	Student 1	5	Class 5	Teacher 5	Room 5	ID #	Student 1001	2
7	counselor 1	10	ID #	Student 1	6	Class 6	Teacher 6	Room 6
8	counselor 1	10	ID #	Student 1	7	Class 7	Teacher 7	Room 7
9	counselor 1	11	ID #	Student 6	1	Class 1six	6Teacher 1	Room 16
10	counselor 1	11	ID #	Student 6	2	Class 2six	6Teacher 2	Room 26
11	counselor 1	11	ID #	Student 6	3	Class 3six	6Teacher 3	Room 36
12	counselor 1	11	ID #	Student 6	4	Class 4sx	6Teacher 4	Room 46
13	counselor 1	11	ID #	Student 6	5	Class 5six	6Teacher 5	Room 56
14	counselor 1	11	ID #	Student 6	6	Class 6six	6Teacher 6	Room 66
15	counselor 1	11	ID #	Student 6	7	Class 7six	6Teacher 7	Room 76
16	counselor 2	11	ID #	Student 890	1	Class 1890	Teacher 11	Room 18
17	counselor 2	11	ID #	Student 890	2	Class 2890	Teacher 21	Room 28
18	counselor 2	11	ID #	Student 890	3	Class 3890	Teacher 31	Room 38
19	counselor 2	11	ID #	Student 890	4	Class 4890	Teacher 41	Room 48
20	counselor 2	11	ID #	Student 890	5	Class 5890	Teacher 51	Room 58
21	counselor 2	11	ID #	Student 890	6	Class 6890	Teacher 61	Room 68
22	counselor 2	11	ID #	Student 890	7	Class 7890	Teacher 7	Room 78

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula L2 {=INDEX(F$2:F$22,MATCH($J2&$K2,$D$2:$D$22&$E$2:$E$22,0))} M2 {=INDEX(G$2:G$22,MATCH($J2&$K2,$D$2:$D$22&$E$2:$E$22,0))} N2 {=INDEX(H$2:H$22,MATCH($J2&$K2,$D$2:$D$22&$E$2:$E$22,0))} L3 {=INDEX(F$2:F$22,MATCH($J3&$K3,$D$2:$D$22&$E$2:$E$22,0))} M3 {=INDEX(G$2:G$22,MATCH($J3&$K3,$D$2:$D$22&$E$2:$E$22,0))} N3 {=INDEX(H$2:H$22,MATCH($J3&$K3,$D$2:$D$22&$E$2:$E$22,0))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Hi Scott,

Is there a way to ignore the students' names in the formula and focus on the student id? What I found out is that the name of each student in the first list is in one cell that include lastname,first name last name; while the name of each student in the second list is in two cells where last name in one cell and the first name in another. The student ID in both list should match.

Thank you,

 

[Scott T]

Using Id#

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Counselor	Grade	Ident	Student Name	Period	Course Title	Teacher	Room	Ident	Student Name	Period	Course Title	Teacher	Room
2	counselor 1	10	1	Student 1	1	Class 1	Teacher 1	Room 1	6	Student 6	2	Class 2six	6Teacher 2	Room 26
3	counselor 1	10	1	Student 1	2	Class 2	Teacher 2	Room 2	890	Student 890	7	Class 7890	Teacher 7	Room 78
4	counselor 1	10	1	Student 1	3	Class 3	Teacher 3	Room 3	ID #	Student 908	2
5	counselor 1	10	1	Student 1	4	Class 4	Teacher 4	Room 4	ID #	Student 1000	2
6	counselor 1	10	1	Student 1	5	Class 5	Teacher 5	Room 5	ID #	Student 1001	2
7	counselor 1	10	1	Student 1	6	Class 6	Teacher 6	Room 6
8	counselor 1	10	1	Student 1	7	Class 7	Teacher 7	Room 7
9	counselor 1	11	6	Student 6	1	Class 1six	6Teacher 1	Room 16
10	counselor 1	11	6	Student 6	2	Class 2six	6Teacher 2	Room 26
11	counselor 1	11	6	Student 6	3	Class 3six	6Teacher 3	Room 36
12	counselor 1	11	6	Student 6	4	Class 4sx	6Teacher 4	Room 46
13	counselor 1	11	6	Student 6	5	Class 5six	6Teacher 5	Room 56
14	counselor 1	11	6	Student 6	6	Class 6six	6Teacher 6	Room 66
15	counselor 1	11	6	Student 6	7	Class 7six	6Teacher 7	Room 76
16	counselor 2	11	890	Student 890	1	Class 1890	Teacher 11	Room 18
17	counselor 2	11	890	Student 890	2	Class 2890	Teacher 21	Room 28
18	counselor 2	11	890	Student 890	3	Class 3890	Teacher 31	Room 38
19	counselor 2	11	890	Student 890	4	Class 4890	Teacher 41	Room 48
20	counselor 2	11	890	Student 890	5	Class 5890	Teacher 51	Room 58
21	counselor 2	11	890	Student 890	6	Class 6890	Teacher 61	Room 68
22	counselor 2	11	890	Student 890	7	Class 7890	Teacher 7	Room 78
Sheet1

Cell Formulas
Range	Formula
L2	{=INDEX(F$2:F$22,MATCH($I2&$K2,$C$2:$C$22&$E$2:$E$22,0))}
L3	{=INDEX(F$2:F$22,MATCH($I3&$K3,$C$2:$C$22&$E$2:$E$22,0))}
M2	{=INDEX(G$2:G$22,MATCH($I2&$K2,$C$2:$C$22&$E$2:$E$22,0))}
M3	{=INDEX(G$2:G$22,MATCH($I3&$K3,$C$2:$C$22&$E$2:$E$22,0))}
N2	{=INDEX(H$2:H$22,MATCH($I2&$K2,$C$2:$C$22&$E$2:$E$22,0))}
N3	{=INDEX(H$2:H$22,MATCH($I3&$K3,$C$2:$C$22&$E$2:$E$22,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[knaimi]

Thanks, Scott. Unfortunately, when I entered the formula, I got the following error message "#N/A"

This is the formula I used in cell N2 "=INDEX(F$2:F$8891,MATCH($J2&$M2,$C$2:$C$8891&$E$2:$E$8891,0))"

F column is class, J is student ID, M is Period, C is student ID, E is period. I left column I blank.

any suggestions?

 

[Scott T]

Did you confirm the formula with CONTROL+SHIFT+ENTER? This is an array formula and you must use CSE. When you look in the formula bar you should she {} around the formula. The formula works for me. The N/A for the last two students is because I did not add them.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Counselor	Grade	Ident	Student Name	Period	Course Title	Teacher	Room		Ident		Student Name	Period	Course Title	Teacher	Room
2	counselor 1	10	1	Student 1	1	Class 1	Teacher 1	Room 1		6		Student 6	2	Class 26	Teacher 26	Room 26
3	counselor 1	10	1	Student 1	2	Class 2	Teacher 2	Room 2		890		Student 890	7	Class 7890	Teacher 7890	Room 7890
4	counselor 1	10	1	Student 1	3	Class 3	Teacher 3	Room 3		908		Student 908	2	Class 2890	Teacher 2890	Room 2890
5	counselor 1	10	1	Student 1	4	Class 4	Teacher 4	Room 4		100		Student 1000	2	#N/A	#N/A	#N/A
6	counselor 1	10	1	Student 1	5	Class 5	Teacher 5	Room 5		1001		Student 1001	2	#N/A	#N/A	#N/A
7	counselor 1	10	1	Student 1	6	Class 6	Teacher 6	Room 6
8	counselor 1	10	1	Student 1	7	Class 7	Teacher 7	Room 7
9	counselor 1	10	2	Student 2	1	Class 1	Teacher 1	Room 1
10	counselor 1	10	2	Student 2	2	Class 2	Teacher 2	Room 2
11	counselor 1	10	2	Student 2	3	Class 3	Teacher 3	Room 3
12	counselor 1	10	2	Student 2	4	Class 4	Teacher 4	Room 4
13	counselor 1	10	2	Student 2	5	Class 5	Teacher 5	Room 5
14	counselor 1	10	2	Student 2	6	Class 6	Teacher 6	Room 6
15	counselor 1	10	2	Student 2	7	Class 7	Teacher 7	Room 7
16	counselor 2	11	6	Student 6	1	Class 16	Teacher 16	Room 16
17	counselor 2	11	6	Student 6	2	Class 26	Teacher 26	Room 26
18	counselor 2	11	6	Student 6	3	Class 36	Teacher 36	Room 36
19	counselor 2	11	6	Student 6	4	Class 46	Teacher 46	Room 46
20	counselor 2	11	6	Student 6	5	Class 56	Teacher 56	Room 56
21	counselor 2	11	6	Student 6	6	Class 66	Teacher 66	Room 66
22	counselor 2	11	6	Student 6	7	Class 76	Teacher 76	Room 76
23	counselor 7	11	7	Student 7	1	Class 17	Teacher 16	Room 17
24	counselor 7	11	7	Student 7	2	Class 27	Teacher 26	Room 27
25	counselor 7	11	7	Student 7	3	Class 37	Teacher 36	Room 37
26	counselor 7	11	7	Student 7	4	Class 47	Teacher 46	Room 47
27	counselor 7	11	7	Student 7	5	Class 57	Teacher 56	Room 57
28	counselor 7	11	7	Student 7	6	Class 67	Teacher 66	Room 67
29	counselor 7	11	7	Student 7	7	Class 77	Teacher 76	Room 77
30	counselor 7	11	890	Student 890	1	Class 1890	Teacher 1890	Room 1890
31	counselor 7	11	890	Student 890	2	Class 2890	Teacher 2890	Room 2890
32	counselor 7	11	890	Student 890	3	Class 3890	Teacher 3890	Room 3890
33	counselor 7	11	890	Student 890	4	Class 4890	Teacher 4890	Room 4890
34	counselor 7	11	890	Student 890	5	Class 5890	Teacher 5890	Room 5890
35	counselor 7	11	890	Student 890	6	Class 6890	Teacher 6890	Room 6890
36	counselor 7	11	890	Student 890	7	Class 7890	Teacher 7890	Room 7890
37	counselor 3	10	908	Student 908	1	Class 1908	Teacher 1890	Room 1890
38	counselor 3	10	908	Student 908	2	Class 2890	Teacher 2890	Room 2890
39	counselor 3	10	908	Student 908	3	Class 3890	Teacher 3890	Room 3890
40	counselor 3	10	908	Student 908	4	Class 4890	Teacher 4890	Room 4890
41	counselor 3	10	908	Student 908	5	Class 5890	Teacher 5890	Room 5890
42	counselor 3	10	908	Student 908	6	Class 6890	Teacher 6890	Room 6890
43	counselor 3	10	908	Student 908	7	Class 7890	Teacher 7890	Room 7890

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula N2 {=INDEX(F$2:F$8891,MATCH($J2&$M2,$C$2:$C$8891&$E$2:$E$8891,0))} O2 {=INDEX(G$2:G$8891,MATCH($J2&$M2,$C$2:$C$8891&$E$2:$E$8891,0))} P2 {=INDEX(H$2:H$8891,MATCH($J2&$M2,$C$2:$C$8891&$E$2:$E$8891,0))} N3 {=INDEX(F$2:F$8891,MATCH($J3&$M3,$C$2:$C$8891&$E$2:$E$8891,0))} O3 {=INDEX(G$2:G$8891,MATCH($J3&$M3,$C$2:$C$8891&$E$2:$E$8891,0))} P3 {=INDEX(H$2:H$8891,MATCH($J3&$M3,$C$2:$C$8891&$E$2:$E$8891,0))} N4 {=INDEX(F$2:F$8891,MATCH($J4&$M4,$C$2:$C$8891&$E$2:$E$8891,0))} O4 {=INDEX(G$2:G$8891,MATCH($J4&$M4,$C$2:$C$8891&$E$2:$E$8891,0))} P4 {=INDEX(H$2:H$8891,MATCH($J4&$M4,$C$2:$C$8891&$E$2:$E$8891,0))} N5 {=INDEX(F$2:F$8891,MATCH($J5&$M5,$C$2:$C$8891&$E$2:$E$8891,0))} O5 {=INDEX(G$2:G$8891,MATCH($J5&$M5,$C$2:$C$8891&$E$2:$E$8891,0))} P5 {=INDEX(H$2:H$8891,MATCH($J5&$M5,$C$2:$C$8891&$E$2:$E$8891,0))} N6 {=INDEX(F$2:F$8891,MATCH($J6&$M6,$C$2:$C$8891&$E$2:$E$8891,0))} O6 {=INDEX(G$2:G$8891,MATCH($J6&$M6,$C$2:$C$8891&$E$2:$E$8891,0))} P6 {=INDEX(H$2:H$8891,MATCH($J6&$M6,$C$2:$C$8891&$E$2:$E$8891,0))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[knaimi]

IT WORKED! I really want to thank you for your patience with me, Scott. I'm not an advance user, but your formula has saved me a lot of time.

I really appreciate your help,