Vlookup double criteria and Date Results

[ruci1225]

I have Worksheet A that has a phone number field and a Date/Time field for Lead data, I then have Worksheet B that has phone number field and a Date/Time field for call data.

I did a vlookup matching phone number between the sheets and bringing back Date/time from sheet B to a new column in Worksheet A called contact time.

Since the call data is sorted older>new it bring back the first match.

this works fine when I have data that is both on the same day as it finds the first match.

The issue is as follows:
When I have data for a 30 day period, and under the following scenrio the lookup is not accurate.

Lead Date is 4/7/2011 (worksheet A) and 10AM (worksheet A)

The Vlookup brings back the first match by phone number and the Lead Date is 4/1/2011 as this person was called for another reason prior to recieving the lead for a new program inquiry. This is not accurate.
I am looking to find the closest match of date/time in worksheet B with Worksheet A so that I can see how long it took for the phone number to be contacted after the Lead Date (But the first time after the lead date)

i hope this makes sense

 

[Aladin Akyurek]

Would you post a scaled-down sample along with the desired result?

 

[ruci1225]

Worksheet A:
Phone Number 555-555-1111
Lead Date: 4/7/2011 8:53AM

Worksleet B:
Phone Number 555-555-1111
Call Date: 4/2/2011 6:43PM

Phone Number 555-555-1111
Call Date: 4/4/2011 1:26PM

Phone Number 555-555-1111
Call Date: 4/7/2011 10:11AM

Phone Number 555-555-1111
Call Date: 4/8/2011 5:45PM

The result I would like is after the vlookup is executed is to bring back 4/8/2011 5:45PM as this is the value closest to (but greater then) the lead date of 4/7/2011 8:53AM in worksheet A

 

[Aladin Akyurek]

Worksheet A:
Phone Number 555-555-1111
Lead Date: 4/7/2011 8:53AM

Worksleet B:
Phone Number 555-555-1111
Call Date: 4/2/2011 6:43PM

Phone Number 555-555-1111
Call Date: 4/4/2011 1:26PM

Phone Number 555-555-1111
Call Date: 4/7/2011 10:11AM

Phone Number 555-555-1111
Call Date: 4/8/2011 5:45PM

The result I would like is after the vlookup is executed is to bring back 4/8/2011 5:45PM as this is the value closest to (but greater then) the lead date of 4/7/2011 8:53AM in worksheet A

Let A2:B5 on SheetB house the sample you provided.

SheetA

A2: 555-555-1111

B2: 4/7/2011 8:53:00 AM

C2, control+shift+enter, not just enter:

=MIN(IF(SheetB!$A$2:$A$5=A2,IF(SheetB!$B$2:$B$5>=B2,SheetB!$B$2:$B$5)))

 

[ruci1225]

Very interesting, I never would have thought to use IF statements.

I may be doing something wrong as I am recieving 0's

=MIN(IF('I3'!J:J=AS2,IF('I3'!K:K>=P2,'I3'!K:K)))

I3 J:J column houses the phone number, AS2 houses the phone number to match, I3 K:K houses the date/time of calls, P2 houses the date/time that matches the phone number (As2)


I appreciate your help

 

[T. Valko]

Very interesting, I never would have thought to use IF statements.

I may be doing something wrong as I am recieving 0's

=MIN(IF('I3'!J:J=AS2,IF('I3'!K:K>=P2,'I3'!K:K)))

I3 J:J column houses the phone number, AS2 houses the phone number to match, I3 K:K houses the date/time of calls, P2 houses the date/time that matches the phone number (As2)


I appreciate your help

Did you array enter the formula?

Normally, you just hit the Enter key to enter the formula but an array formula is different. You have to use a combination of keys to enter an array formula.

Those keys are the CTRL key, the SHIFT key and the ENTER key. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly brackets { }. You can't just type these brackets in, you *must* use the key combo to produce them. Also, anytime you edit an array formula it *must* be re-entered as an array using the key combo.

Also, you should use a smaller more specifc range:

=MIN(IF('I3'!J:J=AS2,IF('I3'!K:K>=P2,'I3'!K:K)))

=MIN(IF('I3'!J2:J100=AS2,IF('I3'!K2:K100>=P2,'I3'!K2:K100)))

An array formula will evaluate every cell referenced. So, in your version of the formula it's processing over 3 million cells!

If you only have data down to row 100 then you're wasting resources when you reference the entire column.

 

[ruci1225]

Thank you, I properly arrayed the formula and it worked.

The reason I am referencing the entire row as this will be the last formula in a macro.

The data in the I3 tab and CE tab will change daily, weekly, monthly...etc but the format will remain the same. The macro will run all formulas to produce proper results. This all works fine, but I am afraid this last formula will take a long time. any ideas?

 

[T. Valko]

Thank you, I properly arrayed the formula and it worked.

The reason I am referencing the entire row as this will be the last formula in a macro.

The data in the I3 tab and CE tab will change daily, weekly, monthly...etc but the format will remain the same. The macro will run all formulas to produce proper results. This all works fine, but I am afraid this last formula will take a long time. any ideas?

You just have to test it to see if the performance is acceptable.

 

[Aladin Akyurek]

Thank you, I properly arrayed the formula and it worked.

The reason I am referencing the entire row as this will be the last formula in a macro.

The data in the I3 tab and CE tab will change daily, weekly, monthly...etc but the format will remain the same. The macro will run all formulas to produce proper results. This all works fine, but I am afraid this last formula will take a long time. any ideas?

In case the array-processing MIN formulas degrade performance, you might want to investigate DMIN, a database function.

 

[ruci1225]

Thank you the Min function worked fine when I limited the search to 70000