Rows, columns, ranks and headers

[jlsw]

I have a series of arrays with 8 columns and 3 rows. I would like to select the top 3 values in each of these sets and return the column header to three extra cells. Every time I try I get near to the solution but not quite the correct one.

So that the cells J2 to J4 contain the top 3 ranked scores regardless of column or row.

Thanks in advance.

 

[vletm]

jlsw
As You have just read Forum Rules:

Site Rules - New Users - Please Read

Hi all, Welcome to the Chandoo.org Forums. Posting Rules & Etiquette The Chandoo.org Forums is a collaborative and happy place to learn and expand your Excel knowledge. The Chandoo.org Forums consist of several Sub-Forums based on the type of question/area of Excel you are interested in...

chandoo.org

You would remember from How to get the Best Results at Chandoo.org
the sentence which starts as For the best/fastest results, Upload a Sample File

 

[pecoflyer]

For largest scores in K2 and pull down =LARGE($B$2:$I$4,ROW()-1)
For heading of largest scores in L2 =INDEX($B$1:$I$1,SUMPRODUCT(COLUMN($B$1:$I$1)*--(($B$2:$I$4)=LARGE($B$2:$I$4,ROW()-1)))-1) and pull down

 

[Peter Bartholomew]

I used this as an exercise in deploying modern Excel.



where both UNPIVOTTED(array, header) and TOP(list, index, count) are Lambda functions.
UNPIVOTTED

= LAMBDA(data,hdr,
    LET(
      m, ROWS(data),
      n, COLUMNS(data),
      k, SEQUENCE(m*n,1,0),
      r, 1+QUOTIENT(k,n),
      c, 1+MOD(k,n),
      u, INDEX(data,r,c),
      h, INDEX(hdr,c),
      CHOOSE({1,2},h,u) )
   )(score, name)

TOP

= LAMBDA(list,idx,n,
      LET(
      sorted, SORT(list, idx,-1 ),
      r, SEQUENCE(n),
      c, SEQUENCE(1,COLUMNS(list)),
      INDEX(sorted, r, c) )
   )

The definition of the Lambda functions are long but comprise trivial steps, each of which is easily read.

 

[jlsw]

Thank you. Both of those have been incredibly helpful.