Index/Match Offset - Help Please

[myith29]

Hello,

I have a table similar to the one below. I created an index/match formula to find the row header for the highest value which works fine. However, in this case the max value repeats multiple times. For this table, I would need to create a second index/match formula that finds the next spot with the max value and return that row header instead. I figure that index/match is still the best formula but how can I tell Excel to look at the row number from the prior index/match results and start with an array after that?

My first formula is as follows: =index(a2:a6,match(e2,d2:d5,0),1)
The second formula needs to translate to: =index(a3:a6,match(e2,d3:d5,0),1)

Thank you,
[TABLE="width: 500"]
<tbody>[TR]
[TD]Letter[/TD]
[TD]Number[/TD]
[TD]Number 2[/TD]
[TD]Total[/TD]
[TD]Max Value[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]5[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[wideboydixon]

Book1
	A	B	C	D	E	F
1	Letter	Number	Number 2	Total	Max Value	Matching Rows
2	a	3	2	5	5	a
3	b	2	2	4		c
4	c	1	4	5
5	d	2	1	3
6	e	3	1	4
Sheet1

Cell Formulas
Range	Formula
D2	=SUM($B2:$C2)
F2	{=IFERROR(INDEX($A$2:$A$6,SMALL(IF($D$2:$D$6=$E$2,ROW($D$2:$D$6)-ROW($D$2)+1),ROWS($F$2:$F2))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[myith29]

Thank you! I did not even consider using an array formula for this!