Find 2nd and 3rd highest numbers in a row and return column header

[centaur87]

[TABLE="width: 670"]
<tbody style="box-sizing: inherit; border: 0px; margin: 0px; padding: 0px; vertical-align: top; -webkit-font-smoothing: antialiased; text-size-adjust: none;">[TR]
[TD]ETF-A[/TD]
[TD]ETF-B[/TD]
[TD]ETF-C[/TD]
[TD]ETF-D[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]27[/TD]
[TD]11[/TD]
[TD]11[/TD]
[/TR]
</tbody>[/TABLE]
[FONT=&quot]I cannot seem to write the formula that would return the column header of the second largest value.

In above Case, first Largest value is 27, it will return ETF-B
But 2nd largest has duplicate values, hence what formula shall i write to return [/FONT][FONT=&quot]the column header of the second largest value.

Best Regards.
[/FONT]

 

[KOKOSEK]

In this case (if 2nd value appeared twice) do you want to display both headers? Like ETF-C, ETF-D?

 

[centaur87]

yes for 2nd largest it should return the first which appears like in this case ETF-C and 3rd Largest it should return the second one as ETF-D

 

[KOKOSEK]

ETF-D is not 3rd is also 2nd...

 

[centaur87]

but i cant skip ETF-D hence i want it as 3rd, or let me how to resolve this.

 

[centaur87]

Output required sample :

[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64, align: left"]1st Large[/TD]
[TD="class: xl66, width: 64, align: left"]2nd Large[/TD]
[TD="class: xl66, width: 64, align: left"]3rd Large[/TD]
[TD="class: xl67, width: 64, align: left"]4th Large[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64, align: left"]ETF-B[/TD]
[TD="class: xl69, width: 64, align: left"]ETF-C[/TD]
[TD="class: xl69, width: 64, align: left"]ETF-D[/TD]
[TD="class: xl70, width: 64, align: left"] ETF-A


[/TD]
[/TR]
[TR]
[TD="class: xl64, width: 64"][/TD]
[TD="class: xl64, width: 64"][/TD]
[TD="class: xl64, width: 64"][/TD]
[TD="class: xl64, width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: left"]OR[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: left"]1st Large[/TD]
[TD="class: xl66, align: left"]2nd Large[/TD]
[TD="class: xl66, align: left"]3rd Large[/TD]
[TD="class: xl67, align: left"]4th Large[/TD]
[/TR]
[TR]
[TD="class: xl68, width: 64, align: left"]ETF-B[/TD]
[TD="class: xl69, width: 64, align: left"]ETF-D[/TD]
[TD="class: xl69, width: 64, align: left"]ETF-C[/TD]
[TD="class: xl70, width: 64, align: left"] ETF-A


[/TD]
[/TR]
</tbody>[/TABLE]

 

[KOKOSEK]

It is your case but to be honest in my opinion if should be:
1st ETF-B
2nd ETF-C, ETF-D
3rd ETF-A

as ETF-C and D has the same value.

 

[sandy666]

maybe something like this:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]ETF-A[/td][td=bgcolor:#5B9BD5]ETF-B[/td][td=bgcolor:#5B9BD5]ETF-C[/td][td=bgcolor:#5B9BD5]ETF-D[/td][td][/td][td=bgcolor:#70AD47]Max[/td][td=bgcolor:#70AD47]Custom[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]

30​

[/td][td=bgcolor:#DDEBF7]

6​

[/td][td=bgcolor:#DDEBF7]

30​

[/td][td=bgcolor:#DDEBF7]

30​

[/td][td][/td][td=bgcolor:#E2EFDA]

30​

[/td][td=bgcolor:#E2EFDA]ETF-A, ETF-C, ETF-D[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td]

6​

[/td][td]ETF-B[/td][/tr]
[/table]

 

[centaur87]

@KOKOSEK, so what will be formula to get the result as per what you have explained

 

[centaur87]

please help me with the formula