Max with index match

Shree1

New Member
Joined
Sep 8, 2014
Messages
31
Hi, I need a bit of help to find the last column header in of the maximum value in row of cells when the max is duplicated.
Max tbtb00tb01tb02tb03
=INDEX($b$1:$e$1,0,MATCH(MAX($b2:$e2),$b2:$e2,0))120130120120
130130
125100100125

The index match function gives me the first column header of the first max value, but I want the column header of the last occurrence of the max value. So, in the above example, I want a formula that will give me tb01 for a2, tb01 for a3 and tb03 for a4.

Any ideas on how to do that. I have an older version of excel and maxifs functions don't work.

Thanks
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,304
Office Version
  1. 365
Platform
  1. Windows
ABCDEFG
1tb00tb01tb02tb03MAX
2120130120120tb01
3130130tb01
4125100100125tb03
Sheet1
Cell Formulas
RangeFormula
G2:G4G2=LOOKUP(2,1/(B2:E2=MAX(B2:E2)),B$1:E$1)
 
Solution

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
2,012
Office Version
  1. 2016
Platform
  1. Windows
Hi Shree1,

Does this do what you want?

Shree1.xlsx
ABCDE
1Max tbtb00tb01tb02tb03
2tb01120130120120
3tb01130130
4tb03125100100125
Sheet1
Cell Formulas
RangeFormula
A2:A4A2=INDEX($B$1:$E$1,AGGREGATE(14,6,COLUMN(B2:E2)-COLUMN(A2)/(B2:E2=MAX(B2:E2)),1))
 

Forum statistics

Threads
1,144,342
Messages
5,723,824
Members
422,518
Latest member
quack_quack

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top