Lookup name, then date within dates

[Donager]

I tried to search for this and was unable to find it. If someone knows of an existing thread, please link me to it as I don't wish to recreate the wheel.

I am trying to create a formula that takes the name in table 1 and looks it up in table 2. If it finds that name, it should look up the coinciding date in table 1 to determine if it is between the start and end date in table 2.

I have tried several nested if statements combined with vlookup, index match and such and not gotten anywhere.

I would appreciate any assistance. Once I figure the look up, I can combine it with my other formulas.

Table 1
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Date
[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]1/19/2018[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]2/20/2018[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]1/15/2018[/TD]
[/TR]
[TR]
[TD]Gus[/TD]
[TD]1/12/2018[/TD]
[/TR]
</tbody>[/TABLE]


Table 2
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]1/1/2018[/TD]
[TD]2/1/2018[/TD]
[/TR]
[TR]
[TD]Fred[/TD]
[TD]12/1/2017[/TD]
[TD]3/20/2018[/TD]
[/TR]
[TR]
[TD]Shirly[/TD]
[TD]1/1/2018[/TD]
[TD]1/15/2018[/TD]
[/TR]
[TR]
[TD]Suzy[/TD]
[TD]2/1/2018[/TD]
[TD]2/25/2018[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

How about?

	A	B	C
Table 1
Name	Date
Bob	Yes
Bill	No
Fred	Yes
Gus	No
Table 2
Name	Start	End
Bob
Fred
Shirly
Suzy

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]1/19/2018[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]2/20/2018[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1/15/2018[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]1/12/2018[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]2/1/2018[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]12/1/2017[/TD]
[TD="align: right"]3/20/2018[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]1/15/2018[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]2/1/2018[/TD]
[TD="align: right"]2/25/2018[/TD]

</tbody>

Sheet3​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C3[/TH]
[TD="align: left"]=IF(COUNTIFS($A$12:$A$15,A3,$B$12:$B$15,"<="&B3,$C$12:$C$15,">="&B3),"Yes","No")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[alansidman]

Here is a VBA solution. Assume table1 and table2 are sheet names. This determines first if the name is in table2 and then determines if the date is within the date range.

Option Explicit


Sub FindBetween()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Table1")
    Set s2 = Sheets("Table2")
    Dim i As Long, lr1 As Long, lr2 As Long
    Dim j As Long
    lr1 = s1.Range("A" & Rows.Count).End(xlUp).Row
    lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
    Dim res As Variant
    For i = 2 To lr1
        Dim found As Range
        Set found = Sheets("Table2").Columns("A").Find(what:=s1.Range("A" & i), LookIn:=xlValues, lookat:=xlWhole)
        If Not found Is Nothing Then
            s1.Range("C" & i) = "Found"
            j = found.Row
            If s1.Range("B" & i) > s2.Range("B" & j) And s1.Range("B" & i) < s2.Range("C" & j) Then
                s1.Range("D" & i) = "Within Date Span"
            End If
        End If
    Next i
End Sub

 

[markmzz]

Another way:

=IF(ISERROR(1/(MEDIAN(INDEX(C$12:D$15,MATCH(B3,B$12:B$15,0),),C3)=C3)),"No","Yes")

Markmzz

 

[Donager]

Eric W, thank you. This works perfectly. I appreciate the assistance.

 

[Donager]

Here is a VBA solution. Assume table1 and table2 are sheet names. This determines first if the name is in table2 and then determines if the date is within the date range.

Option Explicit


Sub FindBetween()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Table1")
    Set s2 = Sheets("Table2")
    Dim i As Long, lr1 As Long, lr2 As Long
    Dim j As Long
    lr1 = s1.Range("A" & Rows.Count).End(xlUp).Row
    lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
    Dim res As Variant
    For i = 2 To lr1
        Dim found As Range
        Set found = Sheets("Table2").Columns("A").Find(what:=s1.Range("A" & i), LookIn:=xlValues, lookat:=xlWhole)
        If Not found Is Nothing Then
            s1.Range("C" & i) = "Found"
            j = found.Row
            If s1.Range("B" & i) > s2.Range("B" & j) And s1.Range("B" & i) < s2.Range("C" & j) Then
                s1.Range("D" & i) = "Within Date Span"
            End If
        End If
    Next i
End Sub

Thank you, alansidman. I intend to work on my VBA scripting and this is a real-world application VBA I can use for reference.

 

[Donager]

Another way:

=IF(ISERROR(1/(MEDIAN(INDEX(C$12:D$15,MATCH(B3,B$12:B$15,0),),C3)=C3)),"No","Yes")

Markmzz

Thank you, Markmzz