Return High Value for multiple instances

Debstated

New Member
Joined
Jan 18, 2017
Messages
9
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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
ABCDEF
1Last NameFirst NameIDEnglish Highest Test ScoreMath Highest Test ScoreScience Highest Test Score
2Fake 1Name8853393022
3Fake 2Name79659359527
4Fake 3Name26159757365
5Fake 4Name80297166127
6Fake 5Name2050421930
7Fake 6Name2209657928
8Fake 7Name73852395246
9Fake 8Name68190591915
10Fake 9Name49933798883
11Fake 10Name49694635167
12Fake 11Name17460615325
13Fake 12Name8953216342
14Fake 13Name50135926462
15Fake 14Name43760835052
16Fake 15Name35263824615
17Fake 16Name66231809780
18Fake 17Name83717523828
19Fake 18Name557526507
20Fake 19Name12345733195
Summary
Cell Formulas
RangeFormula
D2:D20D2=IFNA(MAX(INDEX(INDIRECT("'Raw Data'!D:D"),MATCH($C2,INDIRECT("'Summary'!$C:$C"),0))),"Not Tested")
E2:E20E2=IFNA(MAX(INDEX(INDIRECT("'Raw Data'!E:E"),MATCH($C2,INDIRECT("'Summary'!$C:$C"),0))),"Not Tested")
F2:F20F2=IFNA(MAX(INDEX(INDIRECT("'Raw Data'!F:F"),MATCH($C2,INDIRECT("'Summary'!$C:$C"),0))),"Not Tested")



Raw Data Sheet

Fake Data.xlsx
ABCDEF
1Last NameFirst NameIDEnglishMathScience
2Fake 11Name1746093022
3Fake 5Name20504359527
4Fake 5Name20504757365
5Fake 5Name20504166127
6Fake 6Name2209621930
7Fake 6Name2209657928
8Fake 6Name22096395246
9Fake 3Name26159591915
10Fake 3Name26159798883
11Fake 3Name26159635167
12Fake 15Name35263615325
13Fake 14Name4376016342
14Fake 10Name49694926462
15Fake 9Name49933835052
16Fake 9Name49933824615
17Fake 13Name50135809780
18Fake 18Name55752523828
19Fake 16Name662316507
20Fake 8Name68190733195
21Fake 8Name68190815671
22Fake 7Name73852304442
23Fake 7Name73852399349
24Fake 2Name796591006684
25Fake 2Name7965922453
26Fake 2Name7965987030
27Fake 4Name80297443233
28Fake 4Name802970722
29Fake 4Name802977682
30Fake 17Name8371779167
31Fake 1Name885338364
32Fake 1Name88533517642
33Fake 1Name88533583786
34Fake 12Name89532806868
Raw Data
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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’)
 
Upvote 0
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!
 
Upvote 0
Thanks for that.
How about
Fluff.xlsm
ABCDEF
1Last NameFirst NameIDEnglish Highest Test ScoreMath Highest Test ScoreScience Highest Test Score
2Fake 1Name88533587686
3Fake 2Name796591007084
4Fake 3Name26159798883
5Fake 4Name80297446833
6Fake 5Name20504759565
7Fake 6Name22096399346
8Fake 7Name73852399349
9Fake 8Name68190815695
10Fake 9Name49933835052
11Fake 10Name49694926462
12Fake 11Name1746093022
13Fake 12Name89532806868
14Fake 13Name50135809780
15Fake 14Name4376016342
16Fake 15Name35263615325
17Fake 16Name662316507
18Fake 17Name8371779167
19Fake 18Name55752523828
20Fake 19Name12345000
Sheet2
Cell Formulas
RangeFormula
D2:F20D2=MAXIFS('Raw Data'!D:D,'Raw Data'!$C:$C,$C2)
 
Upvote 0
Solution
Thanks for that.
How about
Fluff.xlsm
ABCDEF
1Last NameFirst NameIDEnglish Highest Test ScoreMath Highest Test ScoreScience Highest Test Score
2Fake 1Name88533587686
3Fake 2Name796591007084
4Fake 3Name26159798883
5Fake 4Name80297446833
6Fake 5Name20504759565
7Fake 6Name22096399346
8Fake 7Name73852399349
9Fake 8Name68190815695
10Fake 9Name49933835052
11Fake 10Name49694926462
12Fake 11Name1746093022
13Fake 12Name89532806868
14Fake 13Name50135809780
15Fake 14Name4376016342
16Fake 15Name35263615325
17Fake 16Name662316507
18Fake 17Name8371779167
19Fake 18Name55752523828
20Fake 19Name12345000
Sheet2
Cell Formulas
RangeFormula
D2:F20D2=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!
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top