Thread: Pulling test score based on date, not highest score.

What I'm trying to do is have a formula pull scores based on the highest overall test score. I have a workbook which might better explain it, but I'll try anyway.

I have a student who took the SATs twice, once in May & another in October. His overall SAT score was better in the October one and I would like a formula to pull only the scores from that October SAT Test. I have the scores split into the sections of the test:

Math
Critical Thinking
Writing
Essay

The formula I'm using right now only seems to pull the highest section score, but I just want it for the date with the highest overall score. The student did better in Writing on the March test, but I still want the October test score to show instead.

This is the formula I am currently using:

{=IF(ISNA(INDEX(Paste!$J$2:$J$10000,MATCH(C$1&$B146, Paste!$A$2:$A$10000&Paste!$G$2:$G$10000,0))), 0, (INDEX(Paste!$J$2:$J$10000,MATCH(C$1&$B146, Paste!$A$2:$A$10000&Paste!$G$2:$G$10000,0))))}

I hope that kind of makes sense. I've been having the hardest time trying to get a formula for this.

Any help would be greatly appreciated. Thanks!

I would strongly suggest you store a ranking of sorts on the Paste sheet.

Given you're using XL2007 you can make use of the SUMIFS & COUNTIFS functions in pref. to Arrays given they are significantly more efficient.

Paste Sheet

L1: Agg. Score
L2: =SUMIFS($J$2:$J$1000,$A$2:$A$1000,$A2,$D$2:$D$1000,$D2,$E$2:$E$1000,$E2)
copied down

M1: Agg. Rank Key
M2: =COUNTIFS($A$2:$A$1000,$A2,$E$2:$E$1000,$E2,$L$2:$L$1000,">"&$L2)
copied down

With the above in place you can then collate your info on 'By Wm' sheet such that:

C146: =SUMIFS(Paste!$J$2:$J$1000,Paste!$A$2:$A$1000,$C$1,Paste!$G$2:$G$1000,$B146,Paste!$M$2:$M$1000,0)
copied down

by my reckoning the results should be 800, 800, 600, 10 rather than 630,550,800,9 ... your prior Array would only ever return the results for the first first test that met requirements (ie name & code) the scores themselves were never used as any basis for selection (ie MAX was never being accounted for).

The use of SUMIFS will be more efficient than the Array and also removes need for error handler (0 will be result should a combination not exist).