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
Raw Data Sheet
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 |