Return High Value for multiple instances

[Debstated]

Hello all! I am going nuts with this data I cannot figure out what I’m doing wrong.

I have students with multiple attempts at a 3 part exam.

I need to create a summary with the students and query the raw data to give me the highest value from each section. I cannot figure out what I am doing wrong in the formula as currently it returns values that are incorrect.

For example Name Fake 19 (Line 20) doesn't even appear in the raw data but it is generating data

Summary Sheet

Fake Data.xlsx
	A	B	C	D	E	F
1	Last Name	First Name	ID	English Highest Test Score	Math Highest Test Score	Science Highest Test Score
2	Fake 1	Name	88533	93	0	22
3	Fake 2	Name	79659	35	95	27
4	Fake 3	Name	26159	75	73	65
5	Fake 4	Name	80297	16	61	27
6	Fake 5	Name	20504	21	93	0
7	Fake 6	Name	22096	5	79	28
8	Fake 7	Name	73852	39	52	46
9	Fake 8	Name	68190	59	19	15
10	Fake 9	Name	49933	79	88	83
11	Fake 10	Name	49694	63	51	67
12	Fake 11	Name	17460	61	53	25
13	Fake 12	Name	89532	1	63	42
14	Fake 13	Name	50135	92	64	62
15	Fake 14	Name	43760	83	50	52
16	Fake 15	Name	35263	82	46	15
17	Fake 16	Name	66231	80	97	80
18	Fake 17	Name	83717	52	38	28
19	Fake 18	Name	55752	6	50	7
20	Fake 19	Name	12345	73	31	95
Summary

Cell Formulas
Range	Formula
D2:D20	D2	=IFNA(MAX(INDEX(INDIRECT("'Raw Data'!D:D"),MATCH($C2,INDIRECT("'Summary'!$C:$C"),0))),"Not Tested")
E2:E20	E2	=IFNA(MAX(INDEX(INDIRECT("'Raw Data'!E:E"),MATCH($C2,INDIRECT("'Summary'!$C:$C"),0))),"Not Tested")
F2:F20	F2	=IFNA(MAX(INDEX(INDIRECT("'Raw Data'!F:F"),MATCH($C2,INDIRECT("'Summary'!$C:$C"),0))),"Not Tested")

Raw Data Sheet

Fake Data.xlsx
	A	B	C	D	E	F
1	Last Name	First Name	ID	English	Math	Science
2	Fake 11	Name	17460	93	0	22
3	Fake 5	Name	20504	35	95	27
4	Fake 5	Name	20504	75	73	65
5	Fake 5	Name	20504	16	61	27
6	Fake 6	Name	22096	21	93	0
7	Fake 6	Name	22096	5	79	28
8	Fake 6	Name	22096	39	52	46
9	Fake 3	Name	26159	59	19	15
10	Fake 3	Name	26159	79	88	83
11	Fake 3	Name	26159	63	51	67
12	Fake 15	Name	35263	61	53	25
13	Fake 14	Name	43760	1	63	42
14	Fake 10	Name	49694	92	64	62
15	Fake 9	Name	49933	83	50	52
16	Fake 9	Name	49933	82	46	15
17	Fake 13	Name	50135	80	97	80
18	Fake 18	Name	55752	52	38	28
19	Fake 16	Name	66231	6	50	7
20	Fake 8	Name	68190	73	31	95
21	Fake 8	Name	68190	81	56	71
22	Fake 7	Name	73852	30	44	42
23	Fake 7	Name	73852	39	93	49
24	Fake 2	Name	79659	100	66	84
25	Fake 2	Name	79659	22	4	53
26	Fake 2	Name	79659	8	70	30
27	Fake 4	Name	80297	44	32	33
28	Fake 4	Name	80297	0	7	22
29	Fake 4	Name	80297	7	68	2
30	Fake 17	Name	83717	79	1	67
31	Fake 1	Name	88533	8	36	4
32	Fake 1	Name	88533	51	76	42
33	Fake 1	Name	88533	58	37	86
34	Fake 12	Name	89532	80	68	68
Raw Data

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Debstated]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thanks I updated this!

 

[Fluff]

Thanks for that.
How about

Fluff.xlsm
	A	B	C	D	E	F
1	Last Name	First Name	ID	English Highest Test Score	Math Highest Test Score	Science Highest Test Score
2	Fake 1	Name	88533	58	76	86
3	Fake 2	Name	79659	100	70	84
4	Fake 3	Name	26159	79	88	83
5	Fake 4	Name	80297	44	68	33
6	Fake 5	Name	20504	75	95	65
7	Fake 6	Name	22096	39	93	46
8	Fake 7	Name	73852	39	93	49
9	Fake 8	Name	68190	81	56	95
10	Fake 9	Name	49933	83	50	52
11	Fake 10	Name	49694	92	64	62
12	Fake 11	Name	17460	93	0	22
13	Fake 12	Name	89532	80	68	68
14	Fake 13	Name	50135	80	97	80
15	Fake 14	Name	43760	1	63	42
16	Fake 15	Name	35263	61	53	25
17	Fake 16	Name	66231	6	50	7
18	Fake 17	Name	83717	79	1	67
19	Fake 18	Name	55752	52	38	28
20	Fake 19	Name	12345	0	0	0
Sheet2

Cell Formulas
Range	Formula
D2:F20	D2	=MAXIFS('Raw Data'!D:D,'Raw Data'!$C:$C,$C2)

 

[Debstated]

Thanks for that.
How about

Fluff.xlsm
	A	B	C	D	E	F
1	Last Name	First Name	ID	English Highest Test Score	Math Highest Test Score	Science Highest Test Score
2	Fake 1	Name	88533	58	76	86
3	Fake 2	Name	79659	100	70	84
4	Fake 3	Name	26159	79	88	83
5	Fake 4	Name	80297	44	68	33
6	Fake 5	Name	20504	75	95	65
7	Fake 6	Name	22096	39	93	46
8	Fake 7	Name	73852	39	93	49
9	Fake 8	Name	68190	81	56	95
10	Fake 9	Name	49933	83	50	52
11	Fake 10	Name	49694	92	64	62
12	Fake 11	Name	17460	93	0	22
13	Fake 12	Name	89532	80	68	68
14	Fake 13	Name	50135	80	97	80
15	Fake 14	Name	43760	1	63	42
16	Fake 15	Name	35263	61	53	25
17	Fake 16	Name	66231	6	50	7
18	Fake 17	Name	83717	79	1	67
19	Fake 18	Name	55752	52	38	28
20	Fake 19	Name	12345	0	0	0
Sheet2

Cell Formulas
Range	Formula
D2:F20	D2	=MAXIFS('Raw Data'!D:D,'Raw Data'!$C:$C,$C2)

That worked! Thank you I have never used maxif. My students don't know you but they thank you!

 

[Fluff]

Glad to help & thanks for the feedback.