Index Match Small with duplicate values

[Gigi007]

Hello!

I am trying to get the product names by store that are the "bottom 3" in the following table:

[TABLE="width: 222"]
<tbody>[TR]
[TD]Product[/TD]
[TD] Store[/TD]
[TD] Budget[/TD]
[TD] %Achieved[/TD]
[/TR]
[TR]
[TD]Soda[/TD]
[TD]A[/TD]
[TD]10.00[/TD]
[TD]46%[/TD]
[/TR]
[TR]
[TD]Juice[/TD]
[TD]A[/TD]
[TD]20.00[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]Coffee[/TD]
[TD]A[/TD]
[TD]25.00[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]Water[/TD]
[TD]A[/TD]
[TD]30.00[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]Tea[/TD]
[TD]A[/TD]
[TD]40.00[/TD]
[TD]0%[/TD]
[/TR]
[TR]
[TD]Soda[/TD]
[TD]B[/TD]
[TD]5.00[/TD]
[TD]10%[/TD]
[/TR]
[TR]
[TD]Juice[/TD]
[TD]B[/TD]
[TD]15.00[/TD]
[TD]90%[/TD]
[/TR]
[TR]
[TD]Coffee[/TD]
[TD]B[/TD]
[TD]60.00[/TD]
[TD]40%[/TD]
[/TR]
[TR]
[TD]Water[/TD]
[TD]B[/TD]
[TD]20.00[/TD]
[TD]30%[/TD]
[/TR]
[TR]
[TD]Tea[/TD]
[TD]B[/TD]
[TD]10.00[/TD]
[TD]0%[/TD]
[/TR]
</tbody>[/TABLE]

Using the following array formula:

=INDEX($A$2:$A$11,MATCH(SMALL(IF($B$2:$B$11=$H$4,IF($C$2:$C$11>0,$D$2:$D$11)),$F5)&$H$4,$D$2:$D$11&'Sample Mr. Excel'!$B$2:$B$11,0))

I get this results:

[TABLE="width: 139"]
<tbody>[TR]
[TD][/TD]
[TD="colspan: 2"]Store[/TD]
[/TR]
[TR]
[TD]Ranking[/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Juice[/TD]
[TD]Tea[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Juice[/TD]
[TD]Soda[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Juice[/TD]
[TD]Water[/TD]
[/TR]
</tbody>[/TABLE]

The problem is that I can't figure out how to make the formula not to show the same product name when value is duplicated.

Please, help!

 

[Eric W]

Welcome to the board!

Maybe:

	A	B	C	D	E	F	G	H	I
Ranking	A	B
Juice	Tea
Coffee	Soda
Water	Water

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Product[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Store[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Budget[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]%Achieved[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Soda[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]46%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Juice[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]20[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Store[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Coffee[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]25[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Water[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]30[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Tea[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]A[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]40[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Soda[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Juice[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]15[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]90%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Coffee[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]60[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]40%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Water[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]20[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]30%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]Tea[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]B[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]10[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]0%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H5[/TH]
[TD="align: left"]{=IFERROR(INDEX($A$2:$A$11,MATCH(AGGREGATE(15,6,IF($B$2:$B$11=H$4,1,NA())*IF($C$2:$C$11>0,1,NA())*IF(COUNTIF(H$4:H4,$A$2:$A$11)=0,1,NA())*$D$2:$D$11,1),IF($B$2:$B$11=H$4,1,NA())*IF($C$2:$C$11>0,1,NA())*IF(COUNTIF(H$4:H4,$A$2:$A$11)=0,1,NA())*$D$2:$D$11,0)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

AGGREGATE was added in Excel 2010, so you'll need that version or newer for this to work. If you don't, I can probably adapt it.

 

[AhoyNC]

For store A you have 4 items that are in the bottom 3.
In the example below the formula in B16 counts the number of items in the bottom 3 by store. Drag this formula across.
The formula in B17 will list the items. Drag this formula down and across as needed.

These formulas are array formulas and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D
1	Product	Store	Budget	%Achieved
2	Soda	A	10	46%
3	Juice	A	20	0%
4	Coffee	A	25	0%
5	Water	A	30	0%
6	Tea	A	40	0%
7	Soda	B	5	10%
8	Juice	B	15	90%
9	Coffee	B	60	40%
10	Water	B	20	30%
11	Tea	B	10	0%
12
13
14
15	Store	A	B
16	# Items in bottom 3	4	3
17	1	Juice	Tea
18	2	Coffee	Soda
19	3	Water	Water
20	4	Tea
21	5
Sheet

 

[AhoyNC]

Looks like my formula in B17 above got cut off.

IF($A17>B$16,"",INDEX($A$2:$A$11,MATCH(SMALL(IF($B$2:$B$11=B$15,IF($C$2:$C$11> 0,RANK($D$2:$D$11,$D$2:$D$11,1)+ROW($D$2:$D$11)/1000)),$A17),IF($B$2:$B$11=B$15,IF($C$2:$C$11> 0,RANK($D$2:$D$11,$D$2:$D$11,1)+ROW($D$2:$D$11)/1000)),0)))

 

[Gigi007]

Oh my God! This is awesome! It works perfectly. Thank you very much!

 

[Eric W]

Glad we could help!