Problem with Index/Match using SMALL with duplicate values.

[dragon6614]

Hello,
I am having trouble correcting this formula to generate the list of names properly.
I have a table called tbl_Delta and inside are many columns. For the purpose of this question, I am trying to return the data from the column [Player] based on the lowest 10 values in the column [Power].
The formula I am using is: =INDEX(Tbl_Delta[Player],MATCH(SMALL(Tbl_Delta[Power],ROWS($C$5:$C5)),Tbl_Delta[Power],0))

The problem is I am getting the "MATCH" on the first instance of the "0" and not getting the second players name.
The table is in a sperate worksheet than this list FYI.
I have tried researching this but having trouble finding the fix. The 3rd and 4th row should be different players.


Any help would be greatly appreciated.

 

[James006]

Hi,

For an Array Formula, you need to use the three keys Control+Shift+Enter ... and not the standard Enter key ...

 

[dragon6614]

James, thanks for the follow up, I tried this and I am still getting the same results as above.
I am pretty sure the problem has to do with the "MATCH" for the "0" in the example above. The match is simply finding the first instance and returning that.

Using the Control+Shift+Enter the formula turns to this but same results.
{=INDEX(Tbl_Delta[Player],MATCH(SMALL(Tbl_Delta[Power],ROWS($C$5:$C8)),Tbl_Delta[Power],0))}
In the table in their are 100 rows, 2 rows have "0" (zero) in the Power column but these are 2 different Players.
The match is looking for the "0" and returning the Player column value on the first match (Player1), this is happening for each row that is zero instead of returning (Player1) in the number 3 and then (Player2) in the number 4 spot from my screenshot above.

 

[AhoyNC]

Maybe something like this:
Note that in parts of the formula you will need to reference the actual cell(s) not table nomenclature for the formula to work.

Book2
	A	B	C	D	E	F	G
1			Player#	Power		Lowest 10	Player#
2			6	448,080		-1348400	2
3			15	2,238,568		-468520	22
4			17	2,496,848		0	1
5			19	2,637,288		0	4
6			11	1,961,401		11000	5
7			16	2,485,112		448080	6
8			18	2,536,908		1176212	7
9			1	0		1418252	8
10			22	(468,520)		1448328	9
11			12	2,089,972		1819464	10
12			10	1,819,464
13			2	(1,348,400)
14			5	11,000
15			9	1,448,328
16			7	1,176,212
17			14	2,180,392
18			13	2,161,914
19			20	2,646,460
20			4	0
21			8	1,418,252
Sheet2

Cell Formulas
Range	Formula
F2:F13	F2	=IF(ROWS($F$2:$F2)>10,"",SMALL(Table2[Power],ROWS($F$2:$F2)))
G2:G14	G2	=IF(F2="","",INDEX(Table2[Player'#],AGGREGATE(15,6,(ROW(Table2[Power])-ROW($D$2)+1)/(Table2[Power]=SMALL(Table2[Power],ROWS($F$2:$F2))),COUNTIF($F$2:F2,F2))))