Match Overlap Date Range and multiple criteria - Need to match extra criteria

[JOEYSCLEE]

Hi, there

I have 2 tabs - Holiday and Result in the attachment and I got the code as below.

Nevertheless, Column A with header "Name" on holiday tab needs to match with country as well in order to find out holiday name on result tab.

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.

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

[p45cal]

No attachment.

[JOEYSCLEE]

No attachment.

Thanks p45cal!! Here is the attachment

[p45cal]

You've added a column, so the original function doesn't look at the right column in the table anymore! (cf. http://www.vbaexpress.com/forum/showthread.php?62809 ).
Change the function to:

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

and call it in a cell, eg.:
=GetHoliday(D2,B2,E2,Holiday!$A$1:$E$22)

Capture3.JPG


I hope it'll be… 'workable'.

[JOEYSCLEE]

Thanks p45cal...It's workable!!