Oct 03 2019 12:01 AM
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.
Oct 03 2019 02:19 AM
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
Oct 03 2019 02:43 AM
unfortunately not 365. using 2016, which doesn't have Sort Function.
any alternative thought?
Oct 03 2019 02:16 PM
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
Oct 03 2019 04:31 PM
Oct 04 2019 04:38 AM
@dougs5220 , you are welcome