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

OliverScheurich

Gold Contributor

Oct 25, 2022

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.

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