Forum Discussion

Copper Contributor

Oct 25, 2022

Max and adjacent data in column

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 ...

excel

Formulas and Functions

bertie395

Copper Contributor

Oct 25, 2022

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

OliverScheurich

Gold Contributor

Oct 25, 2022

bertie395

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

You can try this formula.

bertie395
Copper Contributor
Oct 26, 2022
Many thanks, I had a typo error but all works now.OliverScheurich