INDEX MATCH returns same value multiple times

[manicmighty]

I am trying to create a TOP 5 table using INDEX MATCH and the Top value is returned as 08:00:00 and I have 3 people with this value and the below code returns the same name for each of the three lines:

=INDEX(IntraDayData!B:B,MATCH(E5,IntraDayData!F:F,0))

Can anyone advise what I need to add to the above code to be able to return the second and third names which are in my source data.

Thanks

 

[Aladin Akyurek]

What you like -- that you post yourself a small sample for which you get a solution or try self to figure our from an example?

 

[manicmighty]

Hi Aladin,

Below is a sample of the data I am using:

Column B	Column C	Column D	Column E	Column F
AgentName	LoggedInDate	ScheduledHours	LoggedInTime	ScheduleDifference
Name6	18/04/2018	08:00:00	00:00:00	08:00:00
Name21	18/04/2018	08:00:00	00:00:00	08:00:00
Name24	18/04/2018	08:00:00	00:00:00	08:00:00

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

The formula only returns "Name6" for the three rows that are in the top 5 and I need it to return "Name6" on the first line then "Name21" then "Name24".

Thanks

 

[Aladin Akyurek]

Are we constructing the Top 5 list based ScheduleDifference?

 

[manicmighty]

Yes there around 50+ rows in the data and the examples above are the three names that have 'ScheduleDifference' of 08:00:00 the others are different time values in rank 4 and 5 which show the correct names.

This is how the results look at the moment and 'Name6' currently shows 3 times:

Rank	Date	Agent Name	Schedule Difference	Schedule Adherence
1	18/04/2018	Name6	08:00:00	Non Adherence
2	18/04/2018	Name6	08:00:00	Non Adherence
3	18/04/2018	Name6	08:00:00	Non Adherence
4	18/04/2018	Name19	07:25:39	Non Adherence
5	18/04/2018	Name35	04:00:00	Non Adherence

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

@ manicmighty

Here is the set up for you to produce a Top N list.

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​
1​	AgentName	LoggedInDate	ScheduledHours	LoggedInTime	ScheduleDifference		top
2​	Name6	18/04/2018	8:00:00​	0:00:00​	8:00:00​		5​
3​	Name21	18/04/2018	8:00:00​	0:00:00​	8:00:00​		6​
4​	Name24	18/04/2018	8:00:00​	0:00:00​	8:00:00​		top agents	top diff values
5​	jake	18/04/2018	8:00:00​	0:00:00​	7:25:39​		Name6	8:00:00​
6​	linda	18/04/2018	8:00:00​	0:00:00​	4:00:00​		Name21	8:00:00​
7​	aladin	18/04/2018	8:00:00​	0:00:00​	4:00:00​		Name24	8:00:00​
8​							jake	7:25:39​
9​							linda	4:00:00​
10​							aladin	4:00:00​
11​
12​

In G3 control+shift+enter, not just enter:

=SUM(IF(ISNUMBER(D2:D7),IF(D2:D7>=LARGE(D2:D7,MIN(COUNT(D2:D7),G2)),1)))

This adjusts Top N for ties.

In G5 control+shift+enter, not just enter, and copy down:

=IF($H5="","",INDEX($A$2:$A$7,SMALL(IF($E$2:$E$7=$H5,ROW($A$2:$A$7)-ROW($A$2)+1),COUNTIFS($H$5:H5,H5))))

In H5 just enter and copy down:

=IF(ROWS($H$5:H5)>$G$3,"",LARGE($E$2:$E$7,ROWS($H$5:H5)))