Top Selling Products - ranking product name error

[josephteh]

I am trying to rank a list of top selling products by Period and Item Type as follows:

Top Selling Products.png

While I am able to get the sales values ranked correctly, the product names shown is including item type not selected.

Please help! Thank you in advance!

Joseph

[AliGW]

This should do it:

=LOOKUP(2,1/((Table1[Sales]=H5)*(Table1[Item Type]=$H$2)*(Table1[Period]=$H$1)),Table1[Item Name])

EDIT: Sorry, doesn't work with multiple values with the same rank.

Excel 2016 (Windows) 32 bit

	F	G	H
1	Period		Jan 2019
2	Item Type		A
3
4	No	Item Name	Sales
5	1	Product A3	10,000.00
6	2	Product A9	9,000.00
7	3	Product A6	7,000.00
8	4	Product A6	7,000.00
9	5	Product A8	6,000.00

Sheet: Sheet1

[AliGW]

Try this:

=@INDEX(Table1[Item Name],AGGREGATE(15,6,(ROW(Table1[Sales])-ROW(Table1[[#Headers],[Sales]]))/((Table1[Sales]=H5)*(Table1[Item Type]=$H$2)*(Table1[Period]=$H$1)),COUNTIF(H$5:H5,H5)))

Excel 2016 (Windows) 32 bit

	F	G	H
1	Period		Jan 2019
2	Item Type		A
3
4	No	Item Name	Sales
5	1	Product A3	10,000.00
6	2	Product A9	9,000.00
7	3	Product A5	7,000.00
8	4	Product A6	7,000.00
9	5	Product A8	6,000.00

Sheet: Sheet1

[CARACALLA]

H5=IFERROR(INDEX(Table1[Item Name],SMALL(IF(Table1[Period]=$H$1,IF(Table1[Item Type]=$H$2,IF(Table1[Sales]=$H5,ROW(Table1[Item Name])-ROW($C$2)+1))),COUNTIF($H$5:H5,H5))),"")

control+shift+enter

copy down

[josephteh]

Thank you, AliGW & CARACALLA, both answers work perfectly well! Here's a star for you!

[AliGW]

Glad to help.

Ff that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[josephteh]

Sorry, 1 more question: is it possible to select "All" Item Type instead of either A & B?

[AliGW]

This is a different question. Please start a new thread with a suitable title and workbook attached. Thanks.

[josephteh]

Oh.. I thought I can just add in since it's an extension of my original question.

Ok, will do, thanks AliGW!

[AliGW]

No, it isn't - it's a different question altogether! Thanks for complying.