Returning the column header of max value on per row basis

Question

I have a spreadsheet whereby on a monthly basis I need to return the top product based on a table for that month. I have copied a screenshot of my current setup below. I am currently doing this by creating an additional column (column `H`) which uses the `INDEX`, `MATCH` and `MAX` functions to return the name of the highest product in that row. I then use another `INDEX` `MATCH` as a lookup in cells `K4` and `L4` to return the value for that month. The problem is that my table expands each month as a new row is added and I wanted to find out if there was a way to combine both the formulas into one. So that all I would need to do is update the current and previous months in cells `K3` and `L3`. I have the same setup across quite a few sheets so want to automate as much as possible. Would love some help, ideally without using VBA if possible at all.

Accepted Answer

You could use this formula in `K4` and drag it across to `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))`

Returning the column header of max value on per row basis

Solution

Problem