Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-890523%22%20slang%3D%22en-US%22%3EHow%20to%20Index%20column%20header%20from%20defined%20data%20in%20table%2C%20Large%20to%20small%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-890523%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20have%20a%20table%20that%20has%20multiple%20columns%20listing%20store%20names%2C%20and%20multiple%20rows%20listing%20Product%20names%2C%20and%20the%20sales%20data%20in%20the%20table%20for%20models%20sold%20by%20each%20store.%20I%20have%20created%20a%20formula%20successfully%20to%20list%20out%20the%20highest%20to%20lowest%20sales%20for%20a%20defined%20model%2C%20but%20I%20am%20having%20trouble%20indexing%20the%20store%20to%20which%20each%20listed%20Qty%20was%20sold%20by.%3C%2FP%3E%3CP%3EMy%20formula%20to%20find%20High%20to%20low%20QTY%3A%3C%2FP%3E%3CP%3E%3D%7BIFERROR(IF(MATCH(%24P%241%2C%24A%3A%24A%2C0)%2CLARGE(INDEX(%24A%241%3A%24N%2415%2CMATCH(%24P%241%2C%24A%3A%24A%2C0)%2C%24B%242%3A%24N%2415)%2CROW(1%3A1)))%2C%22%22)%7D%3C%2FP%3E%3CP%3EI%20have%20tried%20multiple%20formula's%20to%20list%20the%20corresponding%20store%20to%20the%20returned%20Qty%20Value%20(bearing%20in%20mind%20the%20sequence%20of%20stores%20returning%20the%20same%20value%2C%20and%20not%20just%20indexing%20the%20same%20store%20name%20for%20multiple%20same%20Qty's).%3C%2FP%3E%3CP%3EFile%20is%20attached.%3C%2FP%3E%3CP%3EAny%20help%20is%20hugely%20appreciated.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-890523%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-890774%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20Index%20column%20header%20from%20defined%20data%20in%20table%2C%20Large%20to%20small%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-890774%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F419257%22%20target%3D%22_blank%22%3E%40dougs5220%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eassuming%20that%20you%20have%20latest%20version%20of%20office%20%2C%20you%20can%20check%20following%20formula%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DINDEX(SORT(TRANSPOSE(FILTER(B1%3AN15%2C(A1%3AA15%3DP1)%2B(A1%3AA15%3D%22PRODUCT%22)))%2C2%2C-1)%2C%2C1)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethe%20formula%20is%20applied%20in%20attached%20file%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-890825%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20Index%20column%20header%20from%20defined%20data%20in%20table%2C%20Large%20to%20small%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-890825%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F368896%22%20target%3D%22_blank%22%3E%40Kodipady%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eunfortunately%20not%20365.%20using%202016%2C%20which%20doesn't%20have%20Sort%20Function.%3C%2FP%3E%3CP%3Eany%20alternative%20thought%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-892302%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20Index%20column%20header%20from%20defined%20data%20in%20table%2C%20Large%20to%20small%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-892302%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F419257%22%20target%3D%22_blank%22%3E%40dougs5220%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20such%20result%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20190px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F135377iC1BB1BD3B1F84652%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eit%20could%20be%3C%2FP%3E%0A%3CP%3Ein%20S3%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DLARGE(INDEX(%24B%242%3A%24N%2415%2CMATCH(%24P%241%2C%24A%242%3A%24A%2415%2C0)%2C0)%2CROW()-ROW(%24S%242))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ein%20T3%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DINDEX(%241%3A%241%2C%0A%20%20AGGREGATE(15%2C6%2C%0A%20%20%20%201%2F(INDEX(%24B%242%3A%24N%2415%2CMATCH(%24P%241%2C%24A%242%3A%24A%2415%2C0)%2C0)%3D%24S3)*COLUMN(%24B%241%3A%24N%241)%2C%0A%20%20%20%20COUNTIFS(%24S%243%3A%24S3%2C%24S3)%0A%20%20)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eand%20drag%20them%20down%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-892564%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20Index%20column%20header%20from%20defined%20data%20in%20table%2C%20Large%20to%20small%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-892564%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20greatly%2C%20Sergei.%3C%2FP%3E%3CP%3EYou%20are%20a%20Legend.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-893179%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20Index%20column%20header%20from%20defined%20data%20in%20table%2C%20Large%20to%20small%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-893179%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F419257%22%20target%3D%22_blank%22%3E%40dougs5220%3C%2FA%3E%26nbsp%3B%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
dougs5220
New Contributor

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 successfully to list out the highest to lowest sales for a defined model, but I am having trouble indexing the store to which each listed Qty was sold by.

My formula to find High to low QTY:

={IFERROR(IF(MATCH($P$1,$A:$A,0),LARGE(INDEX($A$1:$N$15,MATCH($P$1,$A:$A,0),$B$2:$N$15),ROW(1:1))),"")}

I have tried multiple formula's to list the corresponding store to the returned Qty Value (bearing in mind the sequence of stores returning the same value, and not just indexing the same store name for multiple same Qty's).

File is attached.

Any help is hugely appreciated. 

5 Replies

@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 

@Kodipady 

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

any alternative thought?

 

@dougs5220 

For such result

image.png

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

@Sergei Baklan 

Thank you greatly, Sergei.

You are a Legend.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies