Returning the column header of max value on per row basis

excel excel-formula excel-match
ab_s picture ab_s · Feb 25, 2014 · Viewed 16.8k times · Source

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.

Answer

Dmitry Pavliv picture Dmitry Pavliv · Feb 25, 2014

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

Related questions

If two cells match, return value from third

Here's a simple explanation of what I'm having trouble with. Column A: List of 2300 order numbers Column B: Email Address associated with an order number Column C: List of 100 specific order numbers that I need the email address for So, …

Read More
Check if an excel cell exists on another worksheet in a column - and return the contents of a different column

What I want to do is to say if the contents of cell D3 (on current worksheet) exist in column A in the first worksheet (in my case entitled list). (and they always do exist somewhere). Return the contents of …

Read More
Return Max Value of range that is determined by an Index & Match lookup

I need a cell to display the max value of a range who's row is defined by an index and match formula. I know this will be an array function but I'm struggling to get the syntax right. Here is …

Read More