How to reference multiple ranges to a 'match' formula I am using

[Ritik]

Hi, I am trying to create a formula (lets say the formula is in cell B15,) that will look at a date that I have in a cell e.g. cell B2 shows "2" which is formulated using =WORKDAY(DATE(A2,A3,0), 1). Where A2 has "2023" and A3 has "1" which signifies the first month of the year. I then want the formula to look at ranges of cells e.g. D29:M29, D33:M33, D35:M35 and D38:M38 (I will be doing multiple formulas, therefore some may have more and some may have less ranges) and if any of these cells contain the value 2, I would like B15 to show up as "holiday".

I have attempted to try this and one formula works but only work if it is looking at one range of data.
=IF(ISNUMBER(MATCH(DAY(B2),D25:M25,0)),"Holiday","")
if any cell from D25 to M25 contains 2, Holiday will show.

Another formula that works is =IF(SUMPRODUCT((J28:M28=DAY(F2))+(J31:M31=DAY(F2)))>0,"Holiday",""), but this only works for 2 ranges of data.

If anyone is able to assist me, that would be amazing

 

[OaklandJim]

I have a VBA solution that may work for you. Use the function -- IsHoliday -- in a cell. Feed the function with the value sought and up to five ranges to look in for that value. It returns false if the value sought was not found and true if it is. For example this formula looks for 3 in three ranges =IsHoliday(3, I9:J10,L9:L11, M9:O12)

Function IsHoliday( _
    piValueToFind As Long, _
    prDataRange1 As Range, _
    Optional prDataRange2 As Range, _
    Optional prDataRange3 As Range, _
    Optional prDataRange4 As Range, _
    Optional prDataRange5 As Range _
) As Boolean
    
    Dim rCell As Range
    
    Dim iRangeIndex As Long
    
    Dim rDataRange As Range
    
    IsHoliday = False
    
    For iRangeIndex = 1 To 5

        If iRangeIndex = 1 And Not prDataRange1 Is Nothing _
         Then
            Set rDataRange = prDataRange1
        
        ElseIf iRangeIndex = 2 And Not prDataRange2 Is Nothing _
         Then
            Set rDataRange = prDataRange2

        ElseIf iRangeIndex = 3 And Not prDataRange3 Is Nothing _
         Then
            Set rDataRange = prDataRange3

        ElseIf iRangeIndex = 4 And Not prDataRange4 Is Nothing _
         Then
            Set rDataRange = prDataRange4

        ElseIf iRangeIndex = 5 And Not prDataRange5 Is Nothing _
         Then
            Set rDataRange = prDataRange5
        
        End If

        If Not rDataRange Is Nothing _
         Then

            For Each rCell In rDataRange
            
                If rCell.Value = piValueToFind _
                 Then
                    IsHoliday = True
                    Exit For
                End If

            Next rCell
        
        End If
        
        If IsHoliday Then Exit For
        
        Set rDataRange = Nothing
        
    Next iRangeIndex

End Function

 

[Ritik]

I have a VBA solution that may work for you. Use the function -- IsHoliday -- in a cell. Feed the function with the value sought and up to five ranges to look in for that value. It returns false if the value sought was not found and true if it is. For example this formula looks for 3 in three ranges =IsHoliday(3, I9:J10,L9:L11, M9:O12)

Function IsHoliday( _
    piValueToFind As Long, _
    prDataRange1 As Range, _
    Optional prDataRange2 As Range, _
    Optional prDataRange3 As Range, _
    Optional prDataRange4 As Range, _
    Optional prDataRange5 As Range _
) As Boolean
   
    Dim rCell As Range
   
    Dim iRangeIndex As Long
   
    Dim rDataRange As Range
   
    IsHoliday = False
   
    For iRangeIndex = 1 To 5

        If iRangeIndex = 1 And Not prDataRange1 Is Nothing _
         Then
            Set rDataRange = prDataRange1
       
        ElseIf iRangeIndex = 2 And Not prDataRange2 Is Nothing _
         Then
            Set rDataRange = prDataRange2

        ElseIf iRangeIndex = 3 And Not prDataRange3 Is Nothing _
         Then
            Set rDataRange = prDataRange3

        ElseIf iRangeIndex = 4 And Not prDataRange4 Is Nothing _
         Then
            Set rDataRange = prDataRange4

        ElseIf iRangeIndex = 5 And Not prDataRange5 Is Nothing _
         Then
            Set rDataRange = prDataRange5
       
        End If

        If Not rDataRange Is Nothing _
         Then

            For Each rCell In rDataRange
           
                If rCell.Value = piValueToFind _
                 Then
                    IsHoliday = True
                    Exit For
                End If

            Next rCell
       
        End If
       
        If IsHoliday Then Exit For
       
        Set rDataRange = Nothing
       
    Next iRangeIndex

End Function

Thank you, this does work! However, instead of the formula having to sought a value, could I reference a cell such as B2, which has a value in it, as I tried this but it was not working. For example, I would want it to be =IsHoliday(B2, I9:J10,L9:L11, M9:O12), where B2 =WORKDAY(DATE(A2,A3,0), 1).

 

[OaklandJim]

Sorry, I do not understand your issue. I am not sure how you are using Workday function. Might you post real data so I can see what data you are using so, perhaps, I can try to understand what you are trying to accomplish? Maybe show what result you expect from the data as best you can.

You can post a section of a workbook using the excellent MrExcel XL2BB add-in. Details are here: XL2BB - Excel Range to BBCode

 

[Ritik]

DRAFT CCY Holiday Calender - Question.xls
	A	B	C	D	E
1	Jan-23	Mon	Tue	Wed	Thu
2	2023	2	3	4	5
3	1
4
5
6	AED	TRUE	FALSE
7	AUD
8	CAD
9	CHF
10
11
12	AED	2	14	31
13
14
15
16	AED	4	3	14
Jan 23

Cell Formulas
Range	Formula
B1:E1	B1	=CHOOSE(WEEKDAY(B2),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")
B2	B2	=WORKDAY(DATE(A2,A3,0), 1)
C2	C2	=WORKDAY(DATE(A2,A3,0), 2)
D2	D2	=WORKDAY(DATE(A2,A3,0), 3)
E2	E2	=WORKDAY(DATE(A2,A3,0), 4)
B6	B6	=IsHoliday(2,B12:H12,B16:H16)
C6	C6	=IsHoliday(C2,B12:H12,B16:H16)

Here is the sheet I am working with, The code you wrote works well, which is in cell B6, but I am looking to see if instead of typing the number 2 in the formula could I link it to cell B2. such as the example in cell C6.

let me know if you have any more questions

 

[OaklandJim]

Does this do what you want?

Function IsHoliday( _
    prValueToFind As Range, _
    prDataRange1 As Range, _
    Optional prDataRange2 As Range, _
    Optional prDataRange3 As Range, _
    Optional prDataRange4 As Range, _
    Optional prDataRange5 As Range _
) As Boolean
    
    Dim rCell As Range
    
    Dim iRangeIndex As Long
    
    Dim rDataRange As Range
    
    IsHoliday = False
    
    For iRangeIndex = 1 To 5

        If iRangeIndex = 1 And Not prDataRange1 Is Nothing _
         Then
            Set rDataRange = prDataRange1
        
        ElseIf iRangeIndex = 2 And Not prDataRange2 Is Nothing _
         Then
            Set rDataRange = prDataRange2

        ElseIf iRangeIndex = 3 And Not prDataRange3 Is Nothing _
         Then
            Set rDataRange = prDataRange3

        ElseIf iRangeIndex = 4 And Not prDataRange4 Is Nothing _
         Then
            Set rDataRange = prDataRange4

        ElseIf iRangeIndex = 5 And Not prDataRange5 Is Nothing _
         Then
            Set rDataRange = prDataRange5
        
        End If

        If Not rDataRange Is Nothing _
         Then

            For Each rCell In rDataRange
            
                If rCell.Value = prValueToFind.Value _
                 Then
                    IsHoliday = True
                    Exit For
                End If

            Next rCell
        
        End If
        
        If IsHoliday Then Exit For
        
        Set rDataRange = Nothing
        
    Next iRangeIndex

End Function