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

Alistair McBurnie

New Member
Joined
Oct 15, 2020
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,877
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,254
Messages
5,595,097
Members
413,969
Latest member
rashmikant

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top