Combining AGGREGATE function with INDEX/MATCH to return a value based on most recent date

[Alistair McBurnie]

Hello,

Trying to return a most recent test score, per individual, which corresponds to the most recent date. Each test score has its own column; often multiple tests are completed on a single date, yet some may have different dates in which they have been completed. Currently, I've used the below formula which has returned me the test scores for the most recent date for each individual; however, since different tests can be taken on different days, it will return a 0/blank if there is no score for the most recent date in the dataset, instead of referring to the value from when they last completed the test:

{=INDEX('S&P Raw'!DK:DK,MATCH(1,INDEX(('S&P Raw'!E:E=A6)*('S&P Raw'!D:D=MAX(IF('S&P Raw'!E:E=A6,'S&P Raw'!D:D))),0),0))}
Where --> DK:DK = Test score; E:E = Name; A6 = Cell with name; D:D = Date.

I have played around with the AGGREGATE function to see if this could offer a workaround, and currently I have come up with a formula that returns the most recent date for each individual in the dataset:

=AGGREGATE(14,6,($E:$E=A1031)*$D:$D,1)
Where --> E:E = Name column; A1031 = Cell; D:D = Date column.

This, however, needs to be performed for each of the different tests so that the test column refers to the most recent date for that test. Is there a way of joining this with an INDEX/MATCH function so that the test score (e.g., colum DK:DK) is picked up based on the latest date condition, per individual, that has used the AGGREGATE function above?

Any help will be greatly appreciated.

Thanks,

Alistair

 

[pgc01]

Hi Alistair
Welcome to the board

It always helps to post a sample of the data so that we can understand how the data is structured and test.

For ex., is the dates column sorted in ascending order?

If this is the case you would not care about dates, you'd just need the last result of the user.

Remark:
To post data you can use the mrexcel recommended add-in

XL2BB - Excel Range to BBCode

Convert selected range to BBCode to include as a snapshot in your question.

www.mrexcel.com