retrieve column header from max value row

Question

I have two spreadsheets, one containing a database (sheet 1) and on the other one (sheet2) I'd like to retrieve the header of the column tha contains the max value between rows, given a certain ID.

Sheet 1:

       A        B       C        D         E
   1  ID       2020    2021      2022      2023
   2  N-16     0,00    1550,00   1750,00   2200,00  
   3  N-23     0,00    0,00      0,00      20010,00
   4  N-53    100,00   60,00     20,00     80,00

Sheet 2:

       A         B           C                 
   1  ID       Max Value    Year
   2  N-53     100,00       
   3  N-16     2200,00      
   4  N-23     20010,00

But it should look like this:

       A         B           C                 
   1  ID       Max Value    Year
   2  N-53     100,00       2020
   3  N-16     2200,00      2023
   4  N-23     20010,00     2023

I can´t seem to get the year on column C unless I know which row the ID I´d like to match from sheet2 is located on sheet1. Since this database is updated constantly, the IDs are into a random order.

=INDEX(Sheet1!$B$1:$E$1;MATCH(MAX(INDEX((Sheet1!$A$2:$A$4=$A2)*Sheet1!$B$2:$E$4;0));Sheet1!$A$4:$E$4;0))

Is there a way I can modify the last part of the formula to look for every row on sheet1, find the matching ID from sheet2, look for the row with the matching ID on sheet1 and find Max Value, then return the header with the corresponding year? Thanks in advance.

Peter K. Peter K. · Accepted Answer · 2018-11-22T12:55:20

You can use this array formula :
{=INDEX($B$1:$E$1;1;MATCH(MAX(INDEX(($A$2:$A$4=$A13)*$B$2:$E$4;0));OFFSET($B$2:$E$4;MATCH(A13;$A$2:$A$4;0)-1;0;1;);0))}

I kept most of your formula, but the lookup array for your MATCH is calculated with the OFFSET function. This will also work when the ID's are not sorted.
I put all data on the same worksheet for testing, so you'll just have to change the references.

retrieve column header from max value row

2 Answers