Finding the Maximum Value adjacent to multiple maximum values

[WellMax81]

Hi,
Firstly I apologise in advance as I've been trying to find the correct way to find this solution for sometime now, and I'm convinced it's because I'm terrible at articulating the problem.
I am putting together a spreadsheet to track fitness, with a summary sheet of personal bests. So it will find the Maximum value in one column (the weight) and then the maximum value in a second column (the reps in that set) - however, I've been using the XLOOKUP formula with the MAX formula and it's returning not returning the maximum number, it's returning the first number in the column that's adjacent.
The formula I've been using is: =XLOOKUP(MAX('SNG HS Arm Pull Down'!D:D),'SNG HS Arm Pull Down'!D:D,'SNG HS Arm Pull Down'!C:C)
The dataset it's pulling from is shown in Image 1. The result is shown in Image 2 - this is showing as 12, the first number that is associated with that weight, (20), however the largest number in the data is 19 - which is the formula I need help with.

In addition, I would also like to pull the date across as well. I've really hit a wall with this so any help and advice would be really appreciated! Thank you so much in advance

I'm using Excel on M365 on a Mac, I also have it on a PC as I believe the Mac version has some limitations, if this helps

cheers

Anthony

 

[Peter_SSs]

Welcome to the MrExcel board!

For the future: MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

See if you can adapt this (assuming it does what you want)

23 01 14.xlsm
	A	B	C	D	E	F	G	H
1
2	Date		Reps	Weight			PB Reps	PB Date
3	5/01/2023		12	20			12	9/01/2023
4	9/01/2023		10	20
5	9/01/2023		10	24
6	9/01/2023		12	24
7	13/01/2023		12	24
8	13/01/2023		12	20
9	13/01/2023		16	20
PB

Cell Formulas
Range	Formula
G3:H3	G3	=INDEX(SORT(FILTER(CHOOSECOLS(A3:C9,3,1),D3:D9=MAX(D3:D9)),1,-1),1,0)
Dynamic array formulas.

 

[Fluff]

Another option

=TAKE(CHOOSECOLS(SORT(A3:D100,{4,3},-1),3,1),1)