VLOOKUP in a dynamic range

[war4peace]

Hi everyone,

I have been struggiling with this for a couple days now and I am *this* close but then the solution eludes me.
I have two spreadsheets with two columns each (there are more columns but only two are used at the moment):

Spreadsheet 1 (sample):

[TABLE="width: 500"]
<tbody>[TR]
[TD]e-mail address
[/TD]
[TD]timestamp
[/TD]
[/TR]
[TR]
[TD]john.doe@website.com
[/TD]
[TD]01.08.2014 03:10:07
[/TD]
[/TR]
[TR]
[TD]jane.doe@website.com
[/TD]
[TD][TABLE="width: 203"]
<colgroup><col width="203"></colgroup><tbody>[TR]
[TD="class: xl63, width: 203, align: right"]01.08.2014 03:10:10
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]someone.else@website.com
[/TD]
[TD][TABLE="width: 203"]
<colgroup><col width="203"></colgroup><tbody>[TR]
[TD="class: xl65, width: 203, align: right"]01.08.2014 03:10:14[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]another.user@website.com
[/TD]
[TD][TABLE="width: 203"]
<colgroup><col width="203"></colgroup><tbody>[TR]
[TD="class: xl65, width: 203, align: right"]01.08.2014 03:10:21
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Spreadsheet 2 (sample):


[TABLE="width: 500"]
<tbody>[TR]
[TD]e-mail address
[/TD]
[TD]timestamp
[/TD]
[/TR]
[TR]
[TD]john.doe@website.com
[/TD]
[TD][TABLE="width: 203"]
<colgroup><col width="203"></colgroup><tbody>[TR]
[TD="class: xl63, width: 203, align: right"]01.08.2014 03:14:10
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]jane.doe@website.com
[/TD]
[TD][TABLE="width: 203"]
<colgroup><col width="203"></colgroup><tbody>[TR]
[TD="class: xl65, width: 203, align: right"]01.08.2014 03:18:14
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]luke.skywalker@website.com
[/TD]
[TD][TABLE="width: 203"]
<colgroup><col width="203"></colgroup><tbody>[TR]
[TD="class: xl65, width: 203, align: right"]01.08.2014 03:21:21
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]john.doe@website.com
[/TD]
[TD]01.08.2014 06:44:11
[/TD]
[/TR]
</tbody>[/TABLE]

What I need to do is take the first e-mail address from Spreadsheet 1 and verify whether they exist in Spreadsheet 2 but only in a range bounded by the corresponding timestamp in Spreadsheet 1 (lower bound) and the same timestamp + 1h (upper bound).

Example:

John Doe has a timestamp of "01.08.2014 03:10:07" in Spreadsheet 1 and two timestamps in spreadsheet 2. When I look up his e-mail address in spreadsheet 2, I would see two values but the second one doesn't match my lookup constraints, therefore I need to only lookup in the range expanding at most 1h after their Spreadsheet 1 timestamp.

What I tried:

- Match() to obtain the lower bound of the lookup array, and that works:

=MATCH(1;('S1'!$D$2:$D$3493>='S2'!D2)*('S1'!$D$2:$D$3493<'S2'!F2);0)

But I can't obtain the upper bound.

- INDEX combined with MATCH to get the actual value in, but for some reason I end up with a humongous formula which doesn't do much.

The output should be 0 if the e-mail address is not in the range and 1 if it is.

I can create a VBA script which would to what I need but then I'll have to share that file with other people (read: management) and they don't like macros. Sadly, I'm fairly bad at matching formulas together.

Thanks in advance for responding to my question!

 

[war4peace]

Quick update, I wrote a small VBA script which does the job, but still looking for a formula-based approach.