Max with index match

[Shree1]

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 tb	tb00	tb01	tb02	tb03
=INDEX($b$1:$e$1,0,MATCH(MAX($b2:$e2),$b2:$e2,0))	120	130	120	120
	130	130
	125	100	100	125

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

 

[StephenCrump]

	A	B	C	D	E	F	G
1		tb00	tb01	tb02	tb03		MAX
2		120	130	120	120		tb01
3		130	130				tb01
4		125	100	100	125		tb03
Sheet1

Cell Formulas
Range	Formula
G2:G4	G2	=LOOKUP(2,1/(B2:E2=MAX(B2:E2)),B$1:E$1)

 

[Toadstool]

Hi Shree1,

Does this do what you want?

Shree1.xlsx
	A	B	C	D	E
1	Max tb	tb00	tb01	tb02	tb03
2	tb01	120	130	120	120
3	tb01	130	130
4	tb03	125	100	100	125
Sheet1

Cell Formulas
Range	Formula
A2:A4	A2	=INDEX($B$1:$E$1,AGGREGATE(14,6,COLUMN(B2:E2)-COLUMN(A2)/(B2:E2=MAX(B2:E2)),1))

 

[Shree1]

	A	B	C	D	E	F	G
1		tb00	tb01	tb02	tb03		MAX
2		120	130	120	120		tb01
3		130	130				tb01
4		125	100	100	125		tb03
Sheet1

Cell Formulas
Range	Formula
G2:G4	G2	=LOOKUP(2,1/(B2:E2=MAX(B2:E2)),B$1:E$1)

Works perfectly, many thanks!
S