Match Overlap Date Range and multiple criterias

Hi, there

I have 2 tabs - Holiday and Result in the attachment.

On holiday tab, I state different date range and holiday name for each country. Would you please help to advise how I can get the result - holiday name with the exact date on Result tab which falls into holiday.:help

I'd use a User Defined Function to avoid a complicated worksheet formula

Code:

---

Option Explicit

Function GetHoliday(Cntry As String, Date1 As Date, Dates As Range) As String
    Dim vDates As Variant
    Dim i As Long
    Dim s As String
   
    vDates = Intersect(Dates.Parent.UsedRange, Dates).Value
    s = UCase(Cntry)
   
   
    For i = LBound(vDates, 1) + 1 To UBound(vDates, 1)
        vDates(i, 1) = UCase(vDates(i, 1))
        If vDates(i, 1) = s Then
            If vDates(i, 3) <= Date1 And Date1 <= vDates(i, 4) Then
                GetHoliday = vDates(i, 2)
                Exit Function
            End If
        End If
    Next i
   
    GetHoliday = "Not Holiday"
End Function

---

Thanks Paul! It's workable:yes