Forum Discussion

bertie395's avatar
bertie395
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 is.

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

excel
Formulas and Functions

4 Replies

  • OliverScheurich's avatar
    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's avatar
      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

Resources