Help!!! Return Closest Match associated with duplicate IDs compared against start time for each day.

[jesnyc]

The changes our building access system and I am trying to build a work around. Our overnight staff swipe in and out of the floor throughout their shift so there are multiple timestamps for each day. I am trying to find the swipe that closest matches their shift time to find late arrivals.

I am trying to get Excel to search the uniqeIDs (combination of ID and day of the year) and find the time stamps closest to their shift start.

I have two pieces of the puzzle, but can't for the life of me figure out how to put them together.
I found a formula that looks up two criteria to return a single value and a formula that finds closest match, but can't make the mesh.

TWO CRITERIA

=INDEX(C2:C7,MATCH($C$22,IF(B2:B7="Fabres, Shelley",A2:A7),0))

CLOSEST MATCH

=INDEX(C2:C10, MATCH(MIN(ABS(C2:C10-$C$19)), ABS(C2:C10-$C$19), 0))

Any ideas on how to replace the IF statement in the first formula with the closest match formula?
Is there a simpler way to do this?


The whole data set has bout 5,000 rows and can have 10 -20 swipes in a day.
Thanks for all your help. This forum is amazing.
I hope the MRHTML table looks okay.

Excel 2010
	A	B	C	D	E
1	IDandDate	NAME	Swipe	SHIFT START	Closest Match
2	WHK_8845_42219	Fabres, Shelley	8/3/15 5:47 AM	8/3/15 5:45 AM
3	WHK_8845_42219	Fabres, Shelley	8/3/15 12:41 PM	8/3/15 5:45 AM
4	WHK_8845_42219	Fabres, Shelley	8/3/15 2:29 PM	8/3/15 5:45 AM
5	WHK_8845_42220	Fabres, Shelley	8/4/15 5:42 AM	8/4/15 5:40 AM
6	WHK_8845_42220	Fabres, Shelley	8/4/15 11:42 AM	8/4/15 5:40 AM
7	WHK_8845_42220	Fabres, Shelley	8/4/15 2:25 PM	8/4/15 5:40 AM
8	WHK_8845_42221	Fabres, Shelley	8/5/15 5:49 AM	8/5/15 6:00 AM
9	WHK_8845_42221	Fabres, Shelley	8/5/15 2:44 PM	8/5/15 6:00 AM
10	WHK_8845_42222	Fabres, Shelley	8/6/15 5:42 AM	8/6/15 5:40 AM
11	WHK_8845_42222	Fabres, Shelley	8/6/15 2:27 PM	8/6/15 5:40 AM
12	WHK_8845_42227	Fabres, Shelley	8/11/15 5:47 AM	8/11/15 5:45 AM
13	WHK_8845_42227	Fabres, Shelley	8/11/15 9:33 AM	8/11/15 5:45 AM
14	WHK_8845_42227	Fabres, Shelley	8/11/15 2:33 PM	8/11/15 5:45 AM
15	WHK_8845_42228	Fabres, Shelley	8/12/15 5:44 AM	8/12/15 5:45 AM
16	WHK_8845_42228	Fabres, Shelley	8/12/15 2:25 PM	8/12/15 5:45 AM
Sheet1

 

[jesnyc]

And, the names change since there are multiple users.

The table actually would look more like this.

Excel 2010
	A	B	C	D	E
1	IDandDate	NAME	Swipe	SHIFT START	Closest Match
2	WHK_8845_42219	Fabres, Shelley	8/3/15 5:47 AM	8/3/15 5:45 AM
3	WHK_8845_42219	Fabres, Shelley	8/3/15 12:41 PM	8/3/15 5:45 AM
4	WHK_8845_42219	Fabres, Shelley	8/3/15 2:29 PM	8/3/15 5:45 AM
5	WHK_8845_42220	Fabres, Shelley	8/4/15 5:42 AM	8/4/15 5:40 AM
6	WHK_8845_42220	Fabres, Shelley	8/4/15 11:42 AM	8/4/15 5:40 AM
7	WHK_8845_42220	Fabres, Shelley	8/4/15 2:25 PM	8/4/15 5:40 AM
8	WHK_8845_42221	Fabres, Shelley	8/5/15 5:49 AM	8/5/15 6:00 AM
9	WHK_8845_42221	Fabres, Shelley	8/5/15 2:44 PM	8/5/15 6:00 AM
10	WHK_8845_42222	Fabres, Shelley	8/6/15 5:42 AM	8/6/15 5:40 AM
11	WHK_8845_42222	Fabres, Shelley	8/6/15 2:27 PM	8/6/15 5:40 AM
12	WHK_8771_42227	Houston, John	8/11/15 5:47 AM	8/11/15 5:45 AM
13	WHK_8771_42227	Houston, John	8/11/15 9:33 AM	8/11/15 5:45 AM
14	WHK_8771_42227	Houston, John	8/11/15 2:33 PM	8/11/15 5:45 AM
15	WHK_8771_42228	Houston, John	8/12/15 5:44 AM	8/12/15 5:45 AM
16	WHK_8771_42228	Houston, John	8/12/15 2:25 PM	8/12/15 5:45 AM
Sheet1