Formula help required

[Relaxation_Study]

Hi,

I am a teacher and I have been given a list of students who's grades I need to have a closer look at in excel. I have a curated list of students in one column and the raw listing of the students in the next column with the associated grades after that. I want to remove all the students from the raw list and their test scores if they are not in the curated list. Is there a forumula to remove all the students in the raw listing and their associated grades using the curated list? It would save me a lot of time as there are a lot of students.

Thanks

Help required.xlsx
	A	B	C	D	E	F	G
1	Curated list of students	Raw list of students	English test scores	Maths test scores	Science test scores
2	1	1	100	97	90
3	2	2	92	90	95		I need to remove the students from the raw list who are not in the curated list as well as remove their test scores
4	4	3	70	80	76		The student names have been replaced with numerical representation
5	5	4	32	56	36
6	6	5	59	75	67
7	7	6	41	46	63
8	9	7	80	80	78
9	10	8	67	57	56
10	13	9	6	32	35
11	14	10	46	46	53
12	15	11	76	75	53
13	16	12	43	24	68
14	17	13	45	13	74
15	19	14	36	68	35
16	21	15	80	75	96
17	24	16	85	57	73
18	25	17	89	75	69
19		18	54	23	56
20		19	36	58	23
21		20	44	51	75
22		21	76	95	87
23		22	35	24	32
24		23	86	86	68
25		24	31	35	12
26		25	26	32	15
Sheet1

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Curated list of students	Raw list of students	English test scores	Maths test scores	Science test scores		Students	English test scores	Maths test scores	Science test scores
2	1	1	100	97	90		1	100	97	90
3	2	2	92	90	95		2	92	90	95
4	4	3	70	80	76		4	32	56	36
5	5	4	32	56	36		5	59	75	67
6	6	5	59	75	67		6	41	46	63
7	7	6	41	46	63		7	80	80	78
8	9	7	80	80	78		9	6	32	35
9	10	8	67	57	56		10	46	46	53
10	13	9	6	32	35		13	45	13	74
11	14	10	46	46	53		14	36	68	35
12	15	11	76	75	53		15	80	75	96
13	16	12	43	24	68		16	85	57	73
14	17	13	45	13	74		17	89	75	69
15	19	14	36	68	35		19	36	58	23
16	21	15	80	75	96		21	76	95	87
17	24	16	85	57	73		24	31	35	12
18	25	17	89	75	69		25	26	32	15
19		18	54	23	56
20		19	36	58	23
21		20	44	51	75
22		21	76	95	87
23		22	35	24	32
24		23	86	86	68
25		24	31	35	12
26		25	26	32	15
Master

Cell Formulas
Range	Formula
G2:J18	G2	=FILTER(B2:E26,COUNTIFS(A2:A100,B2:B26))
Dynamic array formulas.

 

[kweaver]

How about:

=FILTER(B2:E26,ISNUMBER(MATCH(B2:B26,$A$2:$A$18,0)))

 

[Relaxation_Study]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Curated list of students	Raw list of students	English test scores	Maths test scores	Science test scores		Students	English test scores	Maths test scores	Science test scores
2	1	1	100	97	90		1	100	97	90
3	2	2	92	90	95		2	92	90	95
4	4	3	70	80	76		4	32	56	36
5	5	4	32	56	36		5	59	75	67
6	6	5	59	75	67		6	41	46	63
7	7	6	41	46	63		7	80	80	78
8	9	7	80	80	78		9	6	32	35
9	10	8	67	57	56		10	46	46	53
10	13	9	6	32	35		13	45	13	74
11	14	10	46	46	53		14	36	68	35
12	15	11	76	75	53		15	80	75	96
13	16	12	43	24	68		16	85	57	73
14	17	13	45	13	74		17	89	75	69
15	19	14	36	68	35		19	36	58	23
16	21	15	80	75	96		21	76	95	87
17	24	16	85	57	73		24	31	35	12
18	25	17	89	75	69		25	26	32	15
19		18	54	23	56
20		19	36	58	23
21		20	44	51	75
22		21	76	95	87
23		22	35	24	32
24		23	86	86	68
25		24	31	35	12
26		25	26	32	15
Master

Cell Formulas
Range	Formula
G2:J18	G2	=FILTER(B2:E26,COUNTIFS(A2:A100,B2:B26))
Dynamic array formulas.

Thank you so much, this works! This will save me a lot of time!

 

[Fluff]

Glad we could help & thanks for the feedback.