Max and adjacent data in column

New Contributor

I have a number of rows and am returning the maximum number in a specific row. eg (MAX(A1:A20)). I also wish to return the data from another row that corresponds to the cell where the maximum number is.

eg if the maximum number is in cell A6 I also wish to return the number in cell D6.

4 Replies

@bertie395 

=INDEX($D$1:$D$20,MATCH(MAX($A$1:$A$20),$A$1:$A$20,0))

You can try INDEX and MATCH. An alternative could be VLOOKUP. If you work with Office365 or 2021 you can apply XLOOKUP as well.

max and adjacent data.JPG 

Thank you for your speedy reply.
I explained myself incorrectly and used columns when I should have said rows. My apologies.
ie. the numbers in your column A should be in a row (say row 2) and your letters in column D should be in say row 4.
So i then need to return the maximum value in row 2 (ie 93) and the corresponding letter in row 4 (ie E)
Thank you for your help

@bertie395 

=INDEX($A$4:$Z$4,MATCH(MAX($A$2:$Z$2),$A$2:$Z$2,0))

You can try this formula.

index match.JPG 

Many thanks, I had a typo error but all works now.@Quadruple_Pawn