Formula Support - Top 10 list using Index and Match

[Redlad91]

Hi, I am currently building a top 15 list for a report which is based off a table of data. Currently i have the below formula but it is only showing the first item when there multiple of the same value.

My formula;
=INDEX(Data!A:A,MATCH(LARGE(IF(Data!D:D="HR",Data!B:B),Output!A2),IF(Data!D:D="HR",Data!B:B),0))

I have copied in below my table and then below this is the output i keep on recieving back using the above formula.

Data Table

Colleague ID	Age	Name	Department
12333​	26​	Lewis	HR
12334​	27​	Ryan	Finance
12335​	26​	Louise	HR
12336​	26​	Liam	IT
12337​	26​	Joanne	Finance
12338​	25​	Lisa	IT
12339​	23​	Brian	HR
12340​	27​	Luke	Finance
12341​	26​	James	IT

Output

No	Colleague ID	Age	Name
1​	12333​	26​	Lewis
2​	12333​	26​	Lewis
3​	12339​	23​	Brian
4​	#NUM!​	#NUM!​	#NUM!​
5​	#NUM!​	#NUM!​	#NUM!​
6​	#NUM!​	#NUM!​	#NUM!​

Is someone able to advise where i can edit my formula so it shows up all the unique values or provide a better formula to use which would resolve the issue.

 

[bebo021999]

Book1
	A	B	C	D	E	F	G	H	I	J
1	Colleague ID	Age	Name	Department		No	Colleague ID	Age	Name	Department
2	12333	26	Lewis	HR		1	12333	26	Lewis	HR
3	12334	27	Ryan	Finance		2	12335	26	Louise	HR
4	12335	26	Louise	HR		3	12339	23	Brian	HR
5	12336	26	Liam	IT
6	12337	26	Joanne	Finance
7	12338	25	Lisa	IT
8	12339	23	Brian	HR
9	12340	27	Luke	Finance
10	12341	26	James	IT
Sheet2

Cell Formulas
Range	Formula
G2:J6	G2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($D:$D)/($D:$D="HR"),$F2)),"")
F2:F10	F2	=IF(ROWS($1:1)<=COUNTIF($D:$D,"HR"),MAX($F$1:F1)+1,"")

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Redlad91]

Book1
	A	B	C	D	E	F	G	H	I	J
1	Colleague ID	Age	Name	Department		No	Colleague ID	Age	Name	Department
2	12333	26	Lewis	HR		1	12333	26	Lewis	HR
3	12334	27	Ryan	Finance		2	12335	26	Louise	HR
4	12335	26	Louise	HR		3	12339	23	Brian	HR
5	12336	26	Liam	IT
6	12337	26	Joanne	Finance
7	12338	25	Lisa	IT
8	12339	23	Brian	HR
9	12340	27	Luke	Finance
10	12341	26	James	IT
Sheet2

Cell Formulas
Range	Formula
G2:J6	G2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($D:$D)/($D:$D="HR"),$F2)),"")
F2:F10	F2	=IF(ROWS($1:1)<=COUNTIF($D:$D,"HR"),MAX($F$1:F1)+1,"")

Hi @bebo021999, Thanks for the reply, I have tested this and it does resolve my issue, it just brought up another issue though and was finding the smallest value. I changed value next to the aggregate action to 14 (Large) but this still shown a lower number.

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Colleague ID	Age	Name	Department		No	Colleague ID	Age	Name	Department
2	12333	26	Lewis	HR		1	12333	26	Lewis	HR
3	12334	27	Ryan	Finance		2	12335	26	Louise	HR
4	12335	26	Louise	HR		3	12339	23	Brian	HR
5	12336	26	Liam	IT
6	12337	26	Joanne	Finance
7	12338	25	Lisa	IT
8	12339	23	Brian	HR
9	12340	27	Luke	Finance
10	12341	26	James	IT
11
Main

Cell Formulas
Range	Formula
G2:J4	G2	=LET(f,SORT(FILTER(A2:D10,D2:D10="HR"),2,-1),INDEX(f,SEQUENCE(MIN(ROWS(f),15)),{1,2,3,4}))
Dynamic array formulas.

 

[Redlad91]

@Fluff, that worked perfect, Thankyou.

 

[Fluff]

Glad we could help & thanks for the feedback.