want to extract most recent data from a list based on two criteria

I have a list of fitness testing results that I need to move the most recent time that text has been done with the athlete to another chart to show where their testing level currently is at. When the data set gets updated with new results then the current result chart will update. Ideally I was thinking that using the aggregate function would be the most ideal method but I am having issues getting it to work properly.

Attached is sample data with the chart that I want the data to be entered. Note: the 2 criteria that will need to be used is the athlete name in cell G6 and the Exercise that is in each row in Column I. and the actual results will be populated in column J and K.


Chris

Try this formulas in J7 and K7

Formula:

Please Login or Register  to view this content.

Formula:

Please Login or Register  to view this content.

In J7 and filled down:

=MAXIFS(B:B,A:A,$G$6,D:D,I7)

In K7 and filled down:

=SUMPRODUCT(($A$2:$A$1000=$G$6)*($B$2:$B$1000=J7)*($D$2:$D$1000=I7)*($E$2:$E$1000))

If your version of Excel doesn't support MAXIFS function, you can use an array function like below (you must commit with CTRL+SHIFT+ENTER, not just ENTER):

=MAX(IF((A2:A1000=$G$6)*(D2:D1000=I7),B2:B1000))

When entered properly, the array function will appear with braces around it, i.e { }

Ahh you are a lifesaver. This worked perfectly Thanks for the help