CODEWITHSUNDEEP
excelexcel-formula
variasduv
variasduv

Reputation: 5

retrieve column header from max value row

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.

Upvotes: 0

Views: 129

Answers (2)

W_O_L_F
W_O_L_F

Reputation: 1486

I have not split on two pages but the method should be analogus to what you want. I have changed the formulas according to your suggestion.

=MAX(INDEX($B$2:$E$4;MATCH(A8;$A$2:$A$4;0);0))

enter image description here

=INDEX($B$1:$E$1;MATCH(B8;INDEX($B$2:$E$4;MATCH(A8;$A$2:$A$4;0););0))

enter image description here

Upvotes: 1

Peter K.
Peter K.

Reputation: 960

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.

Upvotes: 1

Related Questions