Index/Match w/ Duplicates

[smwashburn]

Hi All,

I am trying to create a report in excel that has one sheet that ranks criteria from largest to smallest, by referencing a second sheet that stores all the data.

One one sheet I have a list of departments, and their corresponding success rates as a percentage. The current order of the departments on this sheet is simply in numerical order.

On the second sheet I have used an array formula to return all of the percentages in order of largest to smallest, including any duplicates.

On this second sheet I am trying to use an Index/Match formula to generate the corresponding departments. Currently the simple index/match formula I am using will return one department multiple times if the percentage is the same.

Here is the data on sheet one:

	A	B
1	Deptartment	Success Rate
2	525	83
3	527	92
4	540	77
5	550	60
6	551	77
7	552	0
8	570	15
9	580	0

<tbody>
</tbody>

Here is what I currently have on sheet 2. I am trying to create a formula that will retrieve the corresponding department without giving me duplicates.

	A	B
1	Department	Success Rate
2		92
3		83
4		77
5		77
6		60
7		15
8		0
9		0

<tbody>
</tbody>

Thank you in advance for any help!

 

[Weazel]

maybe something like...

Unknown

	A	B
1	Deptartment	Success Rate
2	525	83
3	527	92
4	540	77
5	550	60
6	551	77
7	552	0
8	570	15
9	580	0
10
11	Department	Success Rate
12	527	92
13	525	83
14	540	77
15	551	77
16	550	60
17	570	15
18	552	0
19	580	0

<tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula A12 {=INDEX(Sheet1!$A$2:$A$9,SMALL(IF(Sheet1!$B$2:$B$9=B12,ROW(Sheet1!$A$2:$A$9)-ROW(Sheet1!$A$2)+1),COUNTIF($B$12:B12,B12)))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>