Match Values Based on First Column then Between Values in Next Two Columns

Hi,

I am trying to find values based on the following criteria and need help with formula or script:
For each value in column A on Sheet1 find the matching value in Column A on Sheet2.
Then find row where value in column B on Sheet1 is between values in Columns B and C on Sheet2.
Where there is a match fill in the value from Column D on Sheet2 in Column C of Sheet1.

Attached is a sample file with the first 3 cases filled in manually. Actually sheet has over 500 records, so I can't do this manually. Any help is appreciated.

VBA

Sub test()
    Dim a, e, i As Long, ii As Long, w, temp, dic As Object
    Set dic = CreateObject("Scripting.Dictionary")
    a = Sheets("sheet2").Cells(1).CurrentRegion.Value
    For i = 3 To UBound(a, 1)
        If Not dic.exists(a(i, 1)) Then
            ReDim w(1 To 3, 1 To 1)
        Else
            w = dic(a(i, 1))
            ReDim Preserve w(1 To 3, 1 To UBound(w, 2) + 1)
        End If
        For ii = 2 To 4
            w(ii - 1, UBound(w, 2)) = a(i, ii)
        Next
        dic(a(i, 1)) = w
    Next
    With Sheets("sheet1").Cells(1).CurrentRegion.Offset(1)
        a = .Value
        For i = 1 To UBound(a, 1) - 1
            temp = a(i, 1): a(i, 1) = Empty
            If dic.exists(temp) Then
                For ii = 1 To UBound(dic(temp), 2)
                    If (a(i, 2) >= dic(temp)(1, ii)) * (a(i, 2) <= dic(temp)(2, ii)) Then
                        a(i, 1) = dic(temp)(3, ii): Exit For
                    End If
                Next
            End If
        Next
        .Columns(3).Value = a
    End With
End Sub

Thank you! Works great!