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.
Hi Doug,
There is no easy answer with formulas, this usually happens when the data structure is not tabular.
If the data is properly structured, formulas are much simpler.
See the attached file for a solution, I converted the initial data structure to a tabular structure using power query, then I inserted a pivot table sorted descending to display top products by store. You can add a slicer for Product or store, this will simplify data visualization.
Reports are much more flexible and powerful using built in report tools than using formulas, if the structure is well formed.
Hi Catalin,
Thanks for the reply.
As the data i receive is a lot bigger than the sample section I supplied, and that I have it linked to several pre configured reports, I was trying to get the solution without having to add extra tables and manipulation to drill down.
Thanks for your effort.
Regards,
Doug.
Hi Doug
Try this: In cell Q3 enter this array formula (CTRL+SHIFT+ENTER) and drag down to as many rows as needed.
=INDEX($B$1:$N$1,SMALL(IF(INDIRECT("B"&MATCH($P$1,A:A,0)&":N"&MATCH($P$1,A:A,0))=P3,COLUMN(INDIRECT("B"&MATCH($P$1,A:A,0)&":N"&MATCH($P$1,A:A,0)))-MIN(COLUMN(INDIRECT("B"&MATCH($P$1,A:A,0)&":N"&MATCH($P$1,A:A,0))))+1),COUNTIF($P$3:P3,P3)))
Hope this helps
Sunny
