Return column header when locating top 20 values

[sarrad]

Hello!

I am looking to find the name of the product that has the highest value, second highest value, third highest, etc. all the way to top 20.

I have hundreds of columns and hundreds of rows, but for each row, I want to find the top 20 among the columns. (See attached excel for what I am looking to do).

I think this has something to do with the large function and a match, but am not exactly sure how to do it!

Thank you!
Sarra

[Pete_UK]

I think you are looking for something like this in C10:

=INDEX($C$3:$Q$3,MATCH(LARGE($C$4:$Q$4,COLUMNS($C:C)),$C$4:$Q$4,0))

Copy across as far as you need to. This assumes unique values in row 4.

Hope this helps.

Pete

[FlameRetired]

This uses a helper row and includes non-unique values. In the helper row 8 C8 and across.

=LARGE($4:$4,COLUMNS($C:C))

and array enter this in C10 and fill across until you get blanks or as far as you need.

=IFERROR(INDEX($C$3:$Q$3,SMALL(IF(C$8=$C$4:$Q$4,COLUMN($C:$Q)-MIN(COLUMN($C:$Q))+1),COUNTIF($C$8:C$8,C$8))),"")

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

[quart]

To jump on to this topic: what if they had wanted to include the actual value next to the column header?