Need help with duplicate information showing #N/A.

[sapper]

I am trying to display all states names that are duplicates. With the formula I am using below, it only shows the first state with a specific ranking and all other with the same rank show #N/A.

=INDEX($A$5:$A$58,MATCH(ROWS(B$2:$C17),$C$2:$C$17,0))

The data is in columns A through C. The formula rankings are in columns E and F.

Any help would be greatly appreciated.

State	Issues	Ranking		State	Ranking
AK	2	13		CA	1
AL	24	35		#N/A	1
AR	8	22		#N/A	1
AZ	12	28		CO	9
CA	0	1		#N/A	9
CO	1	9		AK	13
CT	1	9		AR	22
DC	0	1		AZ	28
DE	0	1		FL	30
FL	16	30		AL	35

<tbody>
</tbody>

 

[Eric W]

Try:

	A	B	C	D	E	F
1	State	Issues	Ranking		State	Ranking
2	AK	2	13		CA	1
3	AL	24	35		DC	1
4	AR	8	22		DE	1
5	AZ	12	28		CO	9
6	CA	0	1		CT	9
7	CO	1	9		AK	13
8	CT	1	9		AR	22
9	DC	0	1		AZ	28
10	DE	0	1		FL	30
11	FL	16	30		AL	35

<tbody>
</tbody>

Sheet8​

Worksheet Formulas Cell Formula F2 =SMALL($C$2:$C$11,ROWS(<f $F$2:$F2</f )) <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula E2 {=INDEX($A$2:$A$11,SMALL(IF($C$2:$C$11=F2,ROW($C$2:$C$11)-ROW($C$2)+1),COUNTIF($F$2:$F2,F2)))} <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>