Find nearest match date and time for a particular user

[cormalu]

Hi,

I have two different data sources which gather information on users. One is a telephony recording system and the other is a CRM type system. I have generated CSV dumps from both systems. I would like to build a formula which would use a name and date/time from the CRM sheet and then find an exact match name and the closest date/time match in the telephony sheet.

EG: =function("John Smith", 13/04/2016 17:54) should return either

1. "" if John Smith is not in the telephony sheet
2. 17971652 -- the unique id for the closest matching record to the passed date/time in the telephony system

Thanks in advance

Cormac

 

[Special-K99]

What is the layout of both files?
e.g.
File 1 Columnn A First Name
File 1 Column B Surname
etc

 

[cormalu]

Telephony file example

Call Start Time	Call End Time	Call Duration	Group	Extension Name	Suppressed Call
03/01/2017 08:09	03/01/2017 08:09	00:00:17	Business Support	Paula Cleary	No
03/01/2017 08:09	03/01/2017 08:11	00:01:33	IT Operations	Stephen Waite	No
03/01/2017 08:09	03/01/2017 08:11	00:01:33	Business Support	Paula Cleary	No
03/01/2017 08:20	03/01/2017 08:21	00:01:02	IT Operations	Stephen Waite	No
03/01/2017 08:31	03/01/2017 08:34	00:03:24	QuotesClaims	Claire Louise Murphy	No
03/01/2017 08:34	03/01/2017 08:36	00:02:19	Health Claims	Philip Knight	No
03/01/2017 08:38	03/01/2017 08:38	00:00:54		Mark George	No
03/01/2017 08:39	03/01/2017 08:40	00:00:29	IT Operations	Stephen Waite	No

<colgroup><col width="128" span="6" style="width:96pt"> </colgroup><tbody>
</tbody>

 

[cormalu]

CRM file example

Start Date	Staff Name
05/01/2017 11:26	Amy Rafferty
30/01/2017 11:01	Amy Rafferty

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

 

[cormalu]

So I am looking up "Amy Rafferty" for the indicated date in file 1 -- hope that makes sense??

 

[cormalu]

Not sure if I am following the right protocols -- I really appreciate your help