Obtain Highest Score Based on Two sets of Dated Tests

[richh]

I am trying to match two sets of test scores to find what each student's highest score was in Test A when they took Test B. The expected results are shown in the last two columns of Test Set A. I think I need to do an INDEX MATCH, but I don't know how to incorporate selecting only the rows that have dates on or before the test taken an finding the max value from those selected.

Test Set A is as follows:

StudentID	TestName	TestScore	TestDate	HighestTestBMath	HighestTestBReading
123	Science	100	8/1/2020	100	110
123	Math	99	9/1/2020	200	120
123	Social Studies	111	9/9/2020	300	120

Test Set B is as follows:

StudentID	TestName	TestScore	TestDate
123	Math	100	7/7/2020
123	Math	200	8/7/2020
123	Math	300	9/7/2020
123	Reading	110	7/7/2020
123	Reading	99	7/7/2020
123	Reading	120	8/7/2020
123	Reading	100	9/4/2020

 

[richh]

So far, I have this:

=INDEX('SetB!Table1[[StudentID]:[TestDate]], MATCH(1,([@[St'udentID]]='SetB'!Table1[StudentID])*("Math"='SetB'!Table1[TestName])*([@[TestDate]]>='SetB'!Table1[TestDate]),0),3)

However, that just returns the first row those three conditions are true. The fourth condition I need is hightest test score that matches the three previous conditions.

 

[Eric W]

Please update your user profile to show the version of Excel you're using. It affects the functions that you can use in the result. For example, if you have Excel 2019 or newer, you can use this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2		StudentID	TestName	TestScore	TestDate	HighestTestBMath	HighestTestBReading			StudentID	TestName	TestScore	TestDate
3		123	Science	100	8/1/2020	100	110			123	Math	100	7/7/2020
4		123	Math	99	9/1/2020	200	120			123	Math	200	8/7/2020
5		123	Social Studies	111	9/9/2020	300	120			123	Math	300	9/7/2020
6										123	Reading	110	7/7/2020
7										123	Reading	99	7/7/2020
8										123	Reading	120	8/7/2020
9										123	Reading	100	9/4/2020
10
Sheet1

Cell Formulas
Range	Formula
F3:F5	F3	=MAXIFS(Table2[TestScore],Table2[StudentID],[@StudentID],Table2[TestName],"Math",Table2[TestDate],"<="&[@TestDate])
G3:G5	G3	=MAXIFS(Table2[TestScore],Table2[StudentID],[@StudentID],Table2[TestName],"Reading",Table2[TestDate],"<="&[@TestDate])

If you have an older version, you may need to use an array formula like this:

Book1
	A	B	C	D	E	F	G
1
2		StudentID	TestName	TestScore	TestDate	HighestTestBMath	HighestTestBReading
3		123	Science	100	8/1/2020	100	110
4		123	Math	99	9/1/2020	200	120
5		123	Social Studies	111	9/9/2020	300	120
Sheet1

Cell Formulas
Range	Formula
F3:F5	F3	=MAX(IF((Table2[StudentID]=[@StudentID])*(Table2[TestName]="Math")*(Table2[TestDate]<=[@TestDate]),Table2[TestScore]))
G3:G5	G3	=MAX(IF((Table2[StudentID]=[@StudentID])*(Table2[TestName]="Reading")*(Table2[TestDate]<=[@TestDate]),Table2[TestScore]))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[richh]

Thanks Eric! Worked great.