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.

enter image description here

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.

Can you clarify please what you need? You need formulas for `K4` and `L4` based on month in `K2` and `L2` to get top products? — Dmitry Pavliv, Feb 25 '14 at 16:48
but why not: `=Index($H:$H,MATCH($A:$A,K3,0))` for `k4` and drag it across to `L4`? — Dmitry Pavliv, Feb 25 '14 at 16:59
Thanks for that but it returns an error(#N/A), possibly because we don't have a column reference for the INDEX function(?). I wanted to see if there is a way to eliminate the Top products column and combine that as one? — ab_s, Feb 25 '14 at 17:09
sorry, mistype:) correct one: `=Index($H:$H,MATCH(K3,$A:$A,0))` — Dmitry Pavliv, Feb 25 '14 at 17:10
Thanks for that. that works. is there a way to circumvent the lookup via top product at all? — ab_s, Feb 25 '14 at 17:13

score 2 · Accepted Answer · answered Feb 25 '14 at 17:25

2

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))

answered Feb 25 '14 at 17:25

Dmitry Pavliv

35,333
13
79
80

Returning the column header of max value on per row basis

1 Answers1

Linked