Searching for a formula to display highest score and last date student tested

[Excelself]

I have a database containing hundreds of students. That database has student ID numbers (column A), student last name (column B), students first name (column C), test scores (column D) , and test date (column E) as shown below. I want column F to display the student's ID number, column G, the student's last name, column H , the student's first name, column I, the student's highest score from all the tests he/she has taken and column J, the date when that student last tested. The problem is that the database contains information about a same student who has taken the test several times and therefore his/her ID and name appears multiple times with a different test score and a different test date. I want a formula to identify every student from the database by their ID, last name, first name, highest score, and last date he/she tested'.
Thank you!!!

Below is an example of a database

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]LAST NAME[/TD]
[TD]FIRST NAME[/TD]
[TD]TEST SCORE[/TD]
[TD]TEST DATE[/TD]
[TD]ID[/TD]
[TD]LAST NAME[/TD]
[TD]FIRST NAME[/TD]
[TD]HIGHEST SCORE[/TD]
[TD]LAST TEST DATE[/TD]
[/TR]
[TR]
[TD]1771234[/TD]
[TD]SMITH[/TD]
[TD]ALLEN[/TD]
[TD]450[/TD]
[TD]8/4/19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1771235[/TD]
[TD]ABIL[/TD]
[TD]NOE[/TD]
[TD]562[/TD]
[TD]9/4/19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1771236[/TD]
[TD]VILLA[/TD]
[TD]MARY[/TD]
[TD]489[/TD]
[TD]10/1/19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1771234[/TD]
[TD]SMITH[/TD]
[TD]ALLEN[/TD]
[TD]545[/TD]
[TD]10/8/19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1771234[/TD]
[TD]SMITH[/TD]
[TD]ALLEN[/TD]
[TD]600[/TD]
[TD]9/2/19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1771239[/TD]
[TD]ESQUIVEL[/TD]
[TD]MARIO[/TD]
[TD]520[/TD]
[TD]9/25/19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1771236[/TD]
[TD]VILLA[/TD]
[TD]MARY[/TD]
[TD]596[/TD]
[TD]8/30/19[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

A formula approach below. However, note that since a student is doing the test multiple times it would be possible for them to get their highest (or lowest) score on 2 or more different dates with different EFL values.
If that occurs, my formulas would return the most recent date when the highest/lowest score was achieved & the corresponding EFL on that date (see lowest scores for Mary below).

<b>Test Results</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:68px;" /><col style="width:94px;" /><col style="width:98px;" /><col style="width:102px;" /><col style="width:89px;" /><col style="width:43px;" /><col style="width:68px;" /><col style="width:94px;" /><col style="width:98px;" /><col style="width:50px;" /><col style="width:88px;" /><col style="width:78px;" /><col style="width:49px;" /><col style="width:87px;" /><col style="width:77px;" /><col style="width:89px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td><td >P</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-size:10pt; text-align:right; ">ID</td><td style="font-size:10pt; ">LAST NAME</td><td style="font-size:10pt; ">FIRST NAME</td><td style="font-size:10pt; text-align:right; ">TEST SCORE</td><td style="font-size:10pt; text-align:right; ">TEST DATE</td><td style="font-size:10pt; text-align:right; ">EFL</td><td style="font-size:10pt; text-align:right; ">ID</td><td style="font-size:10pt; ">LAST NAME</td><td style="font-size:10pt; ">FIRST NAME</td><td style="font-size:10pt; text-align:right; ">HIGH</td><td style="font-size:10pt; text-align:right; ">HIGH DATE</td><td style="font-size:10pt; text-align:right; ">HIGH EFL</td><td style="font-size:10pt; text-align:right; ">LOW</td><td style="font-size:10pt; text-align:right; ">LOW DATE</td><td style="font-size:10pt; text-align:right; ">LOW EFL</td><td style="font-size:10pt; text-align:right; ">LAST DATE</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; text-align:right; ">1771234</td><td style="font-size:10pt; ">SMITH</td><td style="font-size:10pt; ">ALLEN</td><td style="font-size:10pt; text-align:right; ">450</td><td style="font-size:10pt; text-align:right; ">4/08/2019</td><td style="font-size:10pt; text-align:right; ">1</td><td style="font-size:10pt; text-align:right; ">1771234</td><td style="font-size:10pt; ">SMITH</td><td style="font-size:10pt; ">ALLEN</td><td style="font-size:10pt; text-align:right; ">600</td><td style="font-size:10pt; text-align:right; ">2/09/2019</td><td style="font-size:10pt; text-align:right; ">5</td><td style="font-size:10pt; text-align:right; ">450</td><td style="font-size:10pt; text-align:right; ">4/08/2019</td><td style="font-size:10pt; text-align:right; ">1</td><td style="font-size:10pt; text-align:right; ">8/10/2019</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; text-align:right; ">1771235</td><td style="font-size:10pt; ">ABIL</td><td style="font-size:10pt; ">NOE</td><td style="font-size:10pt; text-align:right; ">562</td><td style="font-size:10pt; text-align:right; ">4/09/2019</td><td style="font-size:10pt; text-align:right; ">2</td><td style="font-size:10pt; text-align:right; ">1771235</td><td style="font-size:10pt; ">ABIL</td><td style="font-size:10pt; ">NOE</td><td style="font-size:10pt; text-align:right; ">562</td><td style="font-size:10pt; text-align:right; ">4/09/2019</td><td style="font-size:10pt; text-align:right; ">2</td><td style="font-size:10pt; text-align:right; ">562</td><td style="font-size:10pt; text-align:right; ">4/09/2019</td><td style="font-size:10pt; text-align:right; ">2</td><td style="font-size:10pt; text-align:right; ">4/09/2019</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; text-align:right; ">1771236</td><td style="font-size:10pt; ">VILLA</td><td style="font-size:10pt; ">MARY</td><td style="font-size:10pt; text-align:right; ">489</td><td style="font-size:10pt; text-align:right; ">1/10/2019</td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; text-align:right; ">1771236</td><td style="font-size:10pt; ">VILLA</td><td style="font-size:10pt; ">MARY</td><td style="font-size:10pt; text-align:right; ">596</td><td style="font-size:10pt; text-align:right; ">30/08/2019</td><td style="font-size:10pt; text-align:right; ">7</td><td style="font-size:10pt; text-align:right; ">489</td><td style="font-size:10pt; text-align:right; ">1/10/2019</td><td style="font-size:10pt; text-align:right; ">3</td><td style="font-size:10pt; text-align:right; ">1/10/2019</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; text-align:right; ">1771234</td><td style="font-size:10pt; ">SMITH</td><td style="font-size:10pt; ">ALLEN</td><td style="font-size:10pt; text-align:right; ">545</td><td style="font-size:10pt; text-align:right; ">8/10/2019</td><td style="font-size:10pt; text-align:right; ">4</td><td style="font-size:10pt; text-align:right; ">1771239</td><td style="font-size:10pt; ">ESQUIVEL</td><td style="font-size:10pt; ">MARIO</td><td style="font-size:10pt; text-align:right; ">520</td><td style="font-size:10pt; text-align:right; ">25/09/2019</td><td style="font-size:10pt; text-align:right; ">6</td><td style="font-size:10pt; text-align:right; ">520</td><td style="font-size:10pt; text-align:right; ">25/09/2019</td><td style="font-size:10pt; text-align:right; ">6</td><td style="font-size:10pt; text-align:right; ">25/09/2019</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; text-align:right; ">1771234</td><td style="font-size:10pt; ">SMITH</td><td style="font-size:10pt; ">ALLEN</td><td style="font-size:10pt; text-align:right; ">600</td><td style="font-size:10pt; text-align:right; ">2/09/2019</td><td style="font-size:10pt; text-align:right; ">5</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; text-align:right; ">1771239</td><td style="font-size:10pt; ">ESQUIVEL</td><td style="font-size:10pt; ">MARIO</td><td style="font-size:10pt; text-align:right; ">520</td><td style="font-size:10pt; text-align:right; ">25/09/2019</td><td style="font-size:10pt; text-align:right; ">6</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; text-align:right; ">1771236</td><td style="font-size:10pt; ">VILLA</td><td style="font-size:10pt; ">MARY</td><td style="font-size:10pt; text-align:right; ">596</td><td style="font-size:10pt; text-align:right; ">30/08/2019</td><td style="font-size:10pt; text-align:right; ">7</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; text-align:right; ">1771236</td><td style="font-size:10pt; ">VILLA</td><td style="font-size:10pt; ">MARY</td><td style="font-size:10pt; text-align:right; ">489</td><td style="font-size:10pt; text-align:right; ">30/08/2019</td><td style="font-size:10pt; text-align:right; ">8</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >G2</td><td >=IFERROR(INDEX<span style=' color:008000; '>($A$2:$A$100,MATCH<span style=' color:#0000ff; '>(0,INDEX<span style=' color:#ff0000; '>(COUNTIF<span style=' color:#804000; '>($G$1:G1,$A$2:$A$100)</span>+<span style=' color:#804000; '>(A$2:A$100="")</span>,0)</span>,0)</span>)</span>,"")</td></tr><tr><td >H2</td><td >=IF($G2="","",INDEX<span style=' color:008000; '>(B$2:B$100,MATCH<span style=' color:#0000ff; '>($G2,$A$2:$A$100,0)</span>)</span>)</td></tr><tr><td >I2</td><td >=IF($G2="","",INDEX<span style=' color:008000; '>(C$2:C$100,MATCH<span style=' color:#0000ff; '>($G2,$A$2:$A$100,0)</span>)</span>)</td></tr><tr><td >J2</td><td >=IF($G2="","",AGGREGATE<span style=' color:008000; '>(14,6,D$2:D$100/<span style=' color:#0000ff; '>($A$2:$A$100=$G2)</span>,1)</span>)</td></tr><tr><td >K2</td><td >=IF(G2="","",AGGREGATE<span style=' color:008000; '>(14,6,E$2:E$100/<span style=' color:#0000ff; '>(<span style=' color:#ff0000; '>(A$2:A$100=G2)</span>*<span style=' color:#ff0000; '>(D$2:D$100=J2)</span>)</span>,1)</span>)</td></tr><tr><td >L2</td><td >=IF(G2="","",INDEX<span style=' color:008000; '>(F:F,AGGREGATE<span style=' color:#0000ff; '>(15,6,ROW<span style=' color:#ff0000; '>(F$2:F$100)</span>/<span style=' color:#ff0000; '>(<span style=' color:#804000; '>(A$2:A$100=G2)</span>*<span style=' color:#804000; '>(D$2:D$100=J2)</span>*<span style=' color:#804000; '>(E$2:E$100=K2)</span>)</span>,1)</span>)</span>)</td></tr><tr><td >M2</td><td >=IF($G2="","",AGGREGATE<span style=' color:008000; '>(15,6,D$2:D$100/<span style=' color:#0000ff; '>($A$2:$A$100=$G2)</span>,1)</span>)</td></tr><tr><td >N2</td><td >=IF(G2="","",AGGREGATE<span style=' color:008000; '>(14,6,E$2:E$100/<span style=' color:#0000ff; '>(<span style=' color:#ff0000; '>(A$2:A$100=G2)</span>*<span style=' color:#ff0000; '>(D$2:D$100=M2)</span>)</span>,1)</span>)</td></tr><tr><td >O2</td><td >=IF(G2="","",INDEX<span style=' color:008000; '>(F:F,AGGREGATE<span style=' color:#0000ff; '>(15,6,ROW<span style=' color:#ff0000; '>(F$2:F$100)</span>/<span style=' color:#ff0000; '>(<span style=' color:#804000; '>(A$2:A$100=G2)</span>*<span style=' color:#804000; '>(D$2:D$100=M2)</span>*<span style=' color:#804000; '>(E$2:E$100=N2)</span>)</span>,1)</span>)</span>)</td></tr><tr><td >P2</td><td >=IF($G2="","",AGGREGATE<span style=' color:008000; '>(14,6,E$2:E$100/<span style=' color:#0000ff; '>($A$2:$A$100=$G2)</span>,1)</span>)</td></tr></table></td></tr></table>

 

[Excelself]

Sorry for the confusion, the post was meant for Peter. Yes, the ID number is the same for Christopher as rectified below.

Thank you

[TABLE="class: cms_table"]
<tbody>[TR]
[TD]Student Id[/TD]
[TD="width: 19%, bgcolor: #C0C0C0"]Student Last Name[/TD]
[TD="width: 21%, bgcolor: #C0C0C0"]Student First Name[/TD]
[TD="width: 21%, bgcolor: #C0C0C0"]Test Type Cd[/TD]
[TD="width: 11%, bgcolor: #C0C0C0"]Test Dt[/TD]
[TD="width: 12%, bgcolor: #C0C0C0"]Scale Score[/TD]
[TD="width: 5%, bgcolor: #C0C0C0"]Efl[/TD]
[/TR]
[TR]
[TD="width: 11%, bgcolor: #FFFFFF"]1793528[/TD]
[TD="width: 19%, bgcolor: #FFFFFF"]Acevedo[/TD]
[TD="width: 21%, bgcolor: #FFFFFF"]Blessing[/TD]
[TD="width: 21%, bgcolor: #FFFFFF"]TABE 11/12 Reading[/TD]
[TD="width: 11%, bgcolor: #FFFFFF"]9/18/2019[/TD]
[TD="width: 12%, bgcolor: #FFFFFF"]493[/TD]
[TD="width: 5%, bgcolor: #FFFFFF"]2[/TD]
[/TR]
[TR]
[TD="width: 11%, bgcolor: #FFFFFF"]1775820[/TD]
[TD="width: 19%, bgcolor: #FFFFFF"]Acker[/TD]
[TD="width: 21%, bgcolor: #FFFFFF"]Christopher[/TD]
[TD="width: 21%, bgcolor: #FFFFFF"]TABE 11/12 Reading[/TD]
[TD="width: 11%, bgcolor: #FFFFFF"]8/8/2019[/TD]
[TD="width: 12%, bgcolor: #FFFFFF"]482[/TD]
[TD="width: 5%, bgcolor: #FFFFFF"]2[/TD]
[/TR]
[TR]
[TD="width: 11%, bgcolor: #FFFFFF"]1812454[/TD]
[TD="width: 19%, bgcolor: #FFFFFF"]Acosta[/TD]
[TD="width: 21%, bgcolor: #FFFFFF"]Brittany[/TD]
[TD="width: 21%, bgcolor: #FFFFFF"]TABE 11/12 Reading[/TD]
[TD="width: 11%, bgcolor: #FFFFFF"]10/9/2019[/TD]
[TD="width: 12%, bgcolor: #FFFFFF"]486[/TD]
[TD="width: 5%, bgcolor: #FFFFFF"]2[/TD]
[/TR]
[TR]
[TD="width: 11%, bgcolor: #FFFFFF"]1797562[/TD]
[TD="width: 19%, bgcolor: #FFFFFF"]Acosta[/TD]
[TD="width: 21%, bgcolor: #FFFFFF"]Bryan[/TD]
[TD="width: 21%, bgcolor: #FFFFFF"]TABE 11/12 Reading[/TD]
[TD="width: 11%, bgcolor: #FFFFFF"]7/19/2019[/TD]
[TD="width: 12%, bgcolor: #FFFFFF"]477[/TD]
[TD="width: 5%, bgcolor: #FFFFFF"]2[/TD]
[/TR]
[TR]
[TD="width: 11%, bgcolor: #FFFFFF"]1801415[/TD]
[TD="width: 19%, bgcolor: #FFFFFF"]Addison Jr[/TD]
[TD="width: 21%, bgcolor: #FFFFFF"]Delaney[/TD]
[TD="width: 21%, bgcolor: #FFFFFF"]TABE 11/12 Reading[/TD]
[TD="width: 11%, bgcolor: #FFFFFF"]9/11/2019[/TD]
[TD="width: 12%, bgcolor: #FFFFFF"]493[/TD]
[TD="width: 5%, bgcolor: #FFFFFF"]2[/TD]
[/TR]
[TR]
[TD="width: 11%, bgcolor: #CCFFFF"]1790581
[/TD]
[TD="width: 19%, bgcolor: #CCFFFF"]Aguirre[/TD]
[TD="width: 21%, bgcolor: #CCFFFF"]Christopher[/TD]
[TD="width: 21%, bgcolor: #CCFFFF"]TABE 11/12 Reading[/TD]
[TD="width: 11%, bgcolor: #CCFFFF"]7/9/2019[/TD]
[TD="width: 12%, bgcolor: #CCFFFF"]487[/TD]
[TD="width: 5%, bgcolor: #CCFFFF"]2[/TD]
[/TR]
[TR]
[TD="width: 11%, bgcolor: #CCFFFF"]17806581[/TD]
[TD="width: 19%, bgcolor: #CCFFFF"]Aguirre
[/TD]
[TD="width: 21%, bgcolor: #CCFFFF"]Christopher[/TD]
[TD="width: 21%, bgcolor: #CCFFFF"]TABE 11/12 Reading[/TD]
[TD="width: 11%, bgcolor: #CCFFFF"]9/20/2019[/TD]
[TD="width: 12%, bgcolor: #CCFFFF"]482[/TD]
[TD="width: 5%, bgcolor: #CCFFFF"]2[/TD]
[/TR]
[TR]
[TD="width: 11%, bgcolor: #FFFFFF"]1780581
[/TD]
[TD="width: 19%, bgcolor: #CCFFFF"]Aguirre[/TD]
[TD="width: 21%, bgcolor: #CCFFFF"]Christopher[/TD]
[TD="width: 21%, bgcolor: #FFFFFF"]TABE 11/12 Reading[/TD]
[TD="width: 11%, bgcolor: #FFFFFF"]8/12/2019[/TD]
[TD="width: 12%, bgcolor: #FFFFFF"]550[/TD]
[TD="width: 5%, bgcolor: #FFFFFF"]4[/TD]
[/TR]
[TR]
[TD="width: 11%, bgcolor: #CCFFFF"]1618791
[/TD]
[TD="width: 19%, bgcolor: #CCFFFF"]Albarran[/TD]
[TD="width: 21%, bgcolor: #CCFFFF"]Jackielynn[/TD]
[TD="width: 21%, bgcolor: #CCFFFF"]TABE 11/12 Reading[/TD]
[TD="width: 11%, bgcolor: #CCFFFF"]7/9/2019[/TD]
[TD="width: 12%, bgcolor: #CCFFFF"]572[/TD]
[TD="width: 5%, bgcolor: #CCFFFF"]4[/TD]
[/TR]
</tbody>[/TABLE]

 

[sandy666]

still different

and each time you answer use @[screenname]

 

[Excelself]

Thank you Peter, I did not realize you had already answered my post. Will try it and will let you know.

Good Day!