Rank a row, find column header WITH duplicate ranked values

[mrpaw]

HI all. I asked a similar question and was given an excellent reply that worked well except it doesn't work if there are duplicate values. If there are two duplicate values in a row the formula I'm using repeats the first column header for the second value. What I need is to find the second value and corresponding column header. I've been searching and it seems this is a recurring question with no clear resolution. Any ideas? Here is the formula I'm using, thank you Eric W, to find the ranked items and then the column header. =IFERROR(LARGE($B3:$O3,(COLUMNS($R3:R3)+1)/2),"") for the numeric values and the following array formula for the header lookup. {=IF(R3="","",IF(R3>0,INDEX($B$2:$O$2,SMALL(IF($B3:$O3=R3,COLUMN($B$3:$O$3)-COLUMN($B$3)+1),COUNTIF($P3:P3,R3)+1)),""))}

Should be	Rank	Column Detail	Ranked Value
Col D ("yellow")	10	Yellow	.50
Col H ("green")	11	Yellow	.50

<tbody>
</tbody>

The second .50 ranked value should show the column detail of GREEN for Col. H, not repeat Col. D.

See original question on this forum under: [h=1]Rank a row, then put values and column header in a table[/h]

 

[Eric W]

I'm confused by your question. The formula I provided handles duplicate values. If you look at the sample I posted in post 4 of the original thread, it shows duplicate values of 5 in R3 and T3, with different headings in Q3 and S3. The example you show in this thread doesn't show enough detail for me to see what you want. Try to post a more complete example.

 

[mrpaw]

I'm confused by your question. The formula I provided handles duplicate values. If you look at the sample I posted in post 4 of the original thread, it shows duplicate values of 5 in R3 and T3, with different headings in Q3 and S3. The example you show in this thread doesn't show enough detail for me to see what you want. Try to post a more complete example.

---

I've retried the formula and now it's not working. The range is larger so I changed all the range references but still getting duplicate column values on the second instance (duplicate). I must be missing a step. Yes, I am entering the formula as an array.

First Value of 1.00 from BT:

{=IF(BT7="","",IF(BT7>0,INDEX(TOTAL_Name,SMALL(IF($B7:$BI7=BT7,COLUMN($B7:$BI7)-COLUMN($B7)+1),COUNTIF($BL7:$BL7,BT7)+1)),""))}

Where Total_Name is the column header range. The first value in BT should show the column header from Col S


Duplicate value 1.00 from BV:

{=IF(BV7="","",IF(BV7>0,INDEX(TOTAL_Name,SMALL(IF($B7:$BI7=BV7,COLUMN($B7:$BI7)-COLUMN($B7)+1),COUNTIF($BL7:$BL7,BV7)+1)),""))}

The duplicate value in BV should show the column header from Col W

 

[mrpaw]

Found MY error. I needed to adjust to the last rank value cell to the left of the formula cell.

All is working now.