Matching a max value to other data

[bmv505]

Good morning. I have a spreadsheet of career basketball stats organized by player number (Column A in both screenshots). I have the maxifs function down. I am trying to figure out how to get the corresponding date and opponent next to the value.

For example. #13 has a career high 12 minutes, which she has done once on 11/8/2024 vs. Loyola (LA). Is there a way I can get the date and opponent to show up in columns FI and FJ?

 

[Joe4]

OK, using some concepts explained here: INDEX MATCH with Multiple Criteria (With Examples)
here is an example I came up with and results (date in A1:H13, and my results are on row 21):

So, if we want to look up the max minutes details for player 13, I put 13 in cell A21.
Then I put this formula in cell H21 to get the maximum number of minutes they played:

=MAXIFS(H2:H13,A2:A13,A21)

Then, to get the date of that game, I enter this formula in cell E21:

=INDEX(E2:E13,MATCH(A21&H21,A2:A13&H2:H13,0))

Then, to get the date of the opponent of that game, I enter this formula in cell F21:

=INDEX(F2:F13,MATCH(A21&H21,A2:A13&H2:H13,0))

 

[bmv505]

OK, using some concepts explained here: INDEX MATCH with Multiple Criteria (With Examples)
here is an example I came up with and results (date in A1:H13, and my results are on row 21):
View attachment 119246

So, if we want to look up the max minutes details for player 13, I put 13 in cell A21.
Then I put this formula in cell H21 to get the maximum number of minutes they played:

=MAXIFS(H2:H13,A2:A13,A21)

Then, to get the date of that game, I enter this formula in cell E21:

=INDEX(E2:E13,MATCH(A21&H21,A2:A13&H2:H13,0))

Then, to get the date of the opponent of that game, I enter this formula in cell F21:

=INDEX(F2:F13,MATCH(A21&H21,A2:A13&H2:H13,0))

Thats great! Thank you so much

Next question. There are some instances where the player has reached their career high multiple times. Is there a way to get the most recent time?

For example, #20 has had 36 minutes twice, on 3/3/2024, and 3/6/2024. The formula results in the first. Is there a way to get it to the most recent?

 

[Joe4]

That, I do not know. That makes things much harder/trickier.
Since it is a new question, different from the original, it would be best to post it as a new question, so it appears in the "Unanswered threads" listing, and has the best chance of being seen and answered.

 

[Joe4]

Scratch that, I may have a way to do it using the new FILTER and TAKE functions.

So, based on the previous example, remove all formulas from E21:H21 and enter this formula in cell E21:

=TAKE(FILTER(E2:H13,(A2:A13=A21)*(H2:H13=MAXIFS(H2:H13,A2:A13,A21)),""),-1)

which will automatically spill over to the other columns.

Here is my "proof of concept":