Returns the maximum column header for each row

Question

I have a table in which on a monthly basis I need to return the top product based on the table for this month. I copied a screenshot of my current setting below.

I am currently doing this by creating an extra column (column H) that uses functions INDEX, MATCHand MAXto return the name of the highest product on this row.

Then I use the other INDEX MATCHas a cell search K4and L4to return the value for this month.

The problem is that my table expands every month when a new row is added, and I wanted to find out if there is a way to combine both formulas into one. So all I have to do is update the current and previous months in K3and L3. I have the same setup across multiple sheets, so you want to automate as much as possible.

I would like to help, ideally, without using VBA, if at all possible.

Answer 1

You can use this formula in K4and drag it onto L4:

=INDEX($B1:$H1,1,MATCH(MAX(INDEX($B:$H,MATCH(K2,$A:$A,0),0)),INDEX($B:$H,MATCH(K2,$A:$A,0),0),0))