Matching Date to Approximate Date Using Date Threshold

[mmcg923]

I have two sets of data. The first includes the time orders are placed to a call center ('order report' tab). Calls come into the call center from many different phone numbers. In the second data set ('call list' tab), I have calls from one specific number on a website which is routed to the main call center number. Unfortunately, the call center does not attach an ID or keep track of the originating number. I am tasked with taking the calls from 'call list' and doing a best match to actual orders ('order report') placed to determine which orders can reasonably be attributed to my website phone number.

Orders, on average, are placed within 7 minutes of the originating call. The data set showing how I am trying to match the numbers is attached. My 'real' data set will eventually contain a weeks worth of call center numbers. In an ideal world, I would compare one number from 'call list' against the range from 'order report' in order to find a best match.

A quick example of a 'call list' to ' order report' match I want to make would be '12/18/2016 8:51' in 'call list' to '12/18/2016 8:56' from the 'order report' tab.

Any thoughts would be much appreciated. Thus far, I have tried a timevalue formula as well as a couple iterations of a vlookup but no success just yet. Attempts should be clear in the document. Thanks!

[Pete_UK]

You can change the formula in C5 of the call_list sheet to this:

=VLOOKUP(A5, 'order report'!$A$2:$A$82, 1, TRUE)

Format the cell as date/time, and when you copy this down you will still get two that show as #N/A as these occur before your first date/time in the order report sheet, but the other cells will now show the matching date/time.

Hope this helps.

Pete