How to find values that fall between two dates?

[Johncapov]

I have a key of the "Values I'm trying to look up", my "Data" and the date ranges in which they fall.

How would I create a function or VBA tool that will look up the values that match to the "Values I'm trying to look up" along with fall between the two dates?

I'd also like to make the out put comma delimited because multiple values can match from the key based on the date range.

I attempted a INDEX function (I think I am entering the references wrong):

=INDEX($I$3:$I$18,MATCH($I$3:$I$18,K2:L2351,IF(E2>=K$2:K$2351,IF(F2<=K$2:K$2351,IF(Value=K$2:K$2351,1))),0))
I'm a beginner with excel any help would be appreciated.



I saw this vba on another forum but it does not incorporate the match/vlookup function.

Function SingleCellExtract(LookupValue As String, LookupRange As Range, ColumnNumber As Integer, Char As String)
'Updateby20150824
Dim I As Long
Dim xRet As String
For I = 1 To LookupRange.Columns(1).Cells.Count
If LookupRange.Cells(I, 1) = LookupValue Then
If xRet = "" Then
xRet = LookupRange.Cells(I, ColumnNumber) & Char
Else
xRet = xRet & "" & LookupRange.Cells(I, ColumnNumber) & Char
End If
End If
Next
SingleCellExtract = Left(xRet, Len(xRet) - 1)
End Function

 

[JLGWhiz]

Show an example of what you would be looking for (search item) and what you would expect to see as an output, and where you want the output to display.

 

[JLGWhiz]

after further review, maybe this is what you want.

Sub t()
Dim c As Range, fn As Range, r As Range
For Each c In Range("H3", Cells(Rows.Count, "H").End(xlUp))
    Set fn = Range("L:L").Find(c.Value)
        If Not fn Is Nothing Then
            For Each r In Range("E3", Cells(Rows.Count, 5).End(xlUp))
                If fn.Offset(, -1).Value > r.Value And r.Offset(, -1).Value < fn.Offset(, -1).Value Then
                    If r.Offset(, 2) = "" Then
                        r.Offset(, 2) = c.Value
                    Else
                         r.Offset(, 2) = r.Offset(, 2).Value & ", " & c.Value
                    End If
                End If
            Next
        End If
Next
End Sub

 

[Johncapov]

Hi, Thank you very much for the response. I apologize for the delayed reply (was out of office).

I will try this script you created, but what I'm trying to obtain is the codes that fall in column L that falls between the date range found in column E and F and only the ones that match to the code key I2:I18. The output would go in column G hopefully separated by commas depending on how many codes appear.

Hopefully that made it make more sense.

 

[JLGWhiz]

That is what the code I posted is supposed to do, and it tested OK in a mock up.

 

[Johncapov]

Hmm It does not seem to work?

 

[Johncapov]

could you upload the test file you have ?

 

[JLGWhiz]

My test file looked like your example in the OP, except I inadvertantly used Column H instead of Column I for the search items. The code below has been modified to use column I.

Sub t()
Dim c As Range, fn As Range, r As Range
For Each c In Range("I3", Cells(Rows.Count, "I").End(xlUp))
    Set fn = Range("L:L").Find(c.Value)
        If Not fn Is Nothing Then
            For Each r In Range("E3", Cells(Rows.Count, 5).End(xlUp))
                If fn.Offset(, -1).Value > r.Value And r.Offset(, -1).Value < fn.Offset(, -1).Value Then
                    If r.Offset(, 2) = "" Then
                        r.Offset(, 2) = c.Value
                    Else
                         r.Offset(, 2) = r.Offset(, 2).Value & ", " & c.Value
                    End If
                End If
            Next
        End If
Next
End Sub