vLookup For Cells w/ Date & Time, but Give or Take X Seconds

[LokiDucks]

Hi All,

I've got a list of times in Column A on Sheet 1 and a list of times in Column A on Sheet 2.

These times are in MM/DD/YYYY HH:MM:SS format

I want to use vLookup for the times on Sheet 2 to look at the list on Sheet 1 and return the Sheet 1 value if a match is found

HOWEVER

The issue is that there SHOULD be a match on Sheet 2 for every value on Sheet 1, but the times can be off by about 5 seconds, making a normal vLookup impossible

So I need to find a way to return matching values give-or-take 5 seconds

Any ideas on how to tackle this?

Here is a Google spreadsheet that might better illustrate what i'm trying to accomplish:
https://docs.google.com/spreadsheet/ccc?key=0AmnlCXlFuNbbdGNMZmZoRE9JX0xFTExzNDV4SFNubHc

Thanks!

 

[Scorpion Steve]

Hi, I see what you're getting at but no achieve. You can use the TEXT function which has helped me in the past with dates/times and lookups (v or hlookups)
If you cell in A1 is MM/DD/YYYY HH:MM:SS you can place (in B1 for example):

=text(a1,"MM/DD/YYYY/ HH:MM:SS")
OR
=TEXT(A1,"HH:MM:SS"
or any format you would like!

I found that converting to text can make v/hlookups work!
Same with =Text(today(),"DDMMYY") etc
Hope this helps

 

[LokiDucks]

Hi Scorpion Steve,

Thanks for your reply! The key portion of the issue i'm having is getting it to look up the value +/- 5 seconds. I can definitely convert to text to make it easier to lookup, but the values would still have to be exact, and I need to be able to account for that slight variance.

Thanks for your input!

 

[Vidar]

Hi, try this one in cell B2 on sheet 1 and copy down:
=IF(SUMPRODUCT(--(MOD(Sheet2!$A$2:$A$65,1)>=MOD(A2,1)-TIME(0,0,5)),--(MOD(Sheet2!$A$2:$A$65,1)<=MOD(A2,1)+TIME(0,0,5)),Sheet2!$A$2:$A$65)=0,"",SUMPRODUCT(--(MOD(Sheet2!$A$2:$A$65,1)>=MOD(A2,1)-TIME(0,0,5)),--(MOD(Sheet2!$A$2:$A$65,1)<=MOD(A2,1)+TIME(0,0,5)),Sheet2!$A$2:$A$65))

Vidar

 

[LokiDucks]

Hi Vidar,

I think that's incredibly close! Ideally, however, I'd be pasting into Sheet 2 and copying down, returning the value from Sheet 1. Would this just be a matter of changing all the "Sheet2" entries to "Sheet1" in the above formula?

Thank you!

 

[Vidar]

Sorry, read the first post wrong.
Had to adjust the formual. Try this on sheet 2 cell B2 and copy down:
=IF(SUMPRODUCT(--(MOD(Sheet1!$A$2:$A$14,1)>=MOD(A2,1)-TIME(0,0,5)),--(MOD(Sheet1!$A$2:$A$14,1)<=MOD(A2,1)+TIME(0,0,5)),Sheet1!$A$2:$A$14)=0,"",SUMPRODUCT(--(MOD(Sheet1!$A$2:$A$14,1)>=MOD(A2,1)-TIME(0,0,5)),--(MOD(Sheet1!$A$2:$A$14,1)<=MOD(A2,1)+TIME(0,0,5)),Sheet1!$A$2:$A$14))

Vidar

 

[LokiDucks]

Vidar,

Thank you! This pretty much works exactly like I need it to. One thing would just make it perfect: Right now, it just looks at Sheet1 cells A2 - A14. This list is going to grow every week, so ideally it would search all of Column A. I edited the formula to change "$A$2:$A:$14" to "A:A" and i've tried "$A:$A" but both of those just return a #VALUE error. Any ideas there? The only fix I can think of would be to change A14 to something like A104000, but I thought there should be an easier way.

Thanks again!

 

[Vidar]

Hi
Try this. The range is dynamic so you can add or delete rows on Sheet1:
=IF(SUMPRODUCT(--(MOD(INDIRECT("Sheet1!$A$2:$A$"&COUNTA(Sheet1!A:A)),1)>=MOD(A2,1)-TIME(0,0,5)),--(MOD(INDIRECT("Sheet1!$A$2:$A$"&COUNTA(Sheet1!A:A)),1)<=MOD(A2,1)+TIME(0,0,5)),INDIRECT("Sheet1!$A$2:$A$"&COUNTA(Sheet1!A:A)))=0,"",SUMPRODUCT(--(MOD(INDIRECT("Sheet1!$A$2:$A$"&COUNTA(Sheet1!A:A)),1)>=MOD(A2,1)-TIME(0,0,5)),--(MOD(INDIRECT("Sheet1!$A$2:$A$"&COUNTA(Sheet1!A:A)),1)<=MOD(A2,1)+TIME(0,0,5)),INDIRECT("Sheet1!$A$2:$A$"&COUNTA(Sheet1!A:A))))

Vidar

 

[LokiDucks]

Vidar,

This appears to work perfectly! Thank you! Now I'm going to have to spend some time dissecting it to figure out exactly what it all means and WHY it works. I appreciate all the effort you put in here.

If you have the time or desire, would it be easy to make it return either "YES" if it finds a match or "NO" if it doesn't? I tried adding an overall IF function around the entire thing

=IF([Your Formula],"YES","NO")

But while it did return the "YES" outcome where appropriate, I only received a #VALUE error where I'd have liked it to return "NO".

If you don't feel like continuing to mess with this, that's totally understandable, and I appreciate your effort this far!

THanks!

-LokiDucks

 

[LokiDucks]

Hi Vidar,

I think I spoke a little too soon. I was checking the returns a little bit more and I'm noticing that it's basing it's match completely on the time component and not taking into consideration the date component. So when it's looking for a match for "10/29/12 3:35:55 PM" it's finding just the 3:35:55 PM portion in a number of different dates.