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

Name	Date
Bob	1/19/2018
Bill	2/20/2018
Fred	1/15/2018
Gus	1/12/2018

<tbody>
</tbody>

Table 2

Name	Start	End
Bob	1/1/2018	2/1/2018
Fred	12/1/2017	3/20/2018
Shirly	1/1/2018	1/15/2018
Suzy	2/1/2018	2/25/2018

<tbody>
</tbody>

 

[Eric W]

How about?

	A	B	C
1	Table 1
2	Name	Date
3	Bob	1/19/2018	Yes
4	Bill	2/20/2018	No
5	Fred	1/15/2018	Yes
6	Gus	1/12/2018	No
7
8
9
10	Table 2
11	Name	Start	End
12	Bob	1/1/2018	2/1/2018
13	Fred	12/1/2017	3/20/2018
14	Shirly	1/1/2018	1/15/2018
15	Suzy	2/1/2018	2/25/2018

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula C3 =IF(COUNTIFS($A$12:$A$15,A3,$B$12:$B$15,"<="&B3,$C$12:$C$15,">="&B3),"Yes","No") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[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