Forum Discussion

dougs5220's avatar
dougs5220
Copper Contributor
Oct 03, 2019

How to Index column header from defined data in table, Large to small

Hi, I have a table that has multiple columns listing store names, and multiple rows listing Product names, and the sales data in the table for models sold by each store. I have created a formula suc...
Formulas and Functions
Kodipady's avatar
Kodipady
Iron Contributor
Oct 03, 2019

dougs5220 

assuming that you have latest version of office , you can check following formula 

 

=INDEX(SORT(TRANSPOSE(FILTER(B1:N15,(A1:A15=P1)+(A1:A15="PRODUCT"))),2,-1),,1)

 

the formula is applied in attached file 

dougs5220's avatar
dougs5220
Copper Contributor
Oct 03, 2019

Kodipady 

unfortunately not 365. using 2016, which doesn't have Sort Function.

any alternative thought?

 

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Oct 03, 2019

    dougs5220 

    For such result

    it could be

    in S3

    =LARGE(INDEX($B$2:$N$15,MATCH($P$1,$A$2:$A$15,0),0),ROW()-ROW($S$2))

    in T3

    =INDEX($1:$1,
  AGGREGATE(15,6,
    1/(INDEX($B$2:$N$15,MATCH($P$1,$A$2:$A$15,0),0)=$S3)*COLUMN($B$1:$N$1),
    COUNTIFS($S$3:$S3,$S3)
  )
)

    and drag them down

    • dougs5220's avatar
      dougs5220
      Copper Contributor
      Oct 03, 2019

      SergeiBaklan 

      Thank you greatly, Sergei.

      You are a Legend.

Resources