Announcement

Collapse
No announcement yet.

Compare 2 Date ranges when name matches

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Compare 2 Date ranges when name matches



    Hello All,
    I wanted to match the Names in the sheet with other sheet and than match the associated date ranges against those names and return me the value of "match" or "no match"

    As in the attached file which have 3 sheets, Vacation calendar and on call sheets. I would to match name(Column A) in vacation calendar with the names as primary and secondary column in Oncall Emea sheet (Column C & D), and the date rages in both and if date ranges collides and name also matches then it should return a value of "match" if true and "non-match" if false.

    Thanks in advance for looking into this.
    Attached Files

  • #2
    Does this formula in column D work?

    =ISNUMBER(MATCH(1,INDEX(((Table_SUN_EMEA[EMEA Primary]=A2)+(Table_SUN_EMEA[EMEA Backup]=A2))*((Table_SUN_EMEA[Start Date]>=B2)*(Table_SUN_EMEA[Start Date]<=C2)+(Table_SUN_EMEA[Start Date]<=B2)*(Table_SUN_EMEA[End Date]>=B2)),0),0))
    Where there is a will there are many ways. Finding one that works for you is the challenge!

    MS Excel MVP 2010-2016

    I am participating in the "Walk for a Cure" for the Kidney Foundation of Canada. If you can/want to, please Donate: https://kidney.akaraisin.com/ui/1674...cipant/1145052

    Comment


    • #3
      It worked like charm Mate. Thanks for solution.

      However at same time we wanted to look into the other sheet as well for vacation check. So i have added another column which tells in which sheet we should search for requester name.

      =IF(ISERROR(IF(D2="OnCall EMEA",ISNUMBER(MATCH(1,INDEX((('Oncall Emea'!C:C=A2)+('Oncall Emea'!D:D=A2))*(('Oncall Emea'!A:A>=B2)*('Oncall Emea'!A:A<=C2)+('Oncall Emea'!A:A<=B2)*('Oncall Emea'!B:B>=B2)),0),0)),If(D2="OnCall AMER",ISNUMBER(MATCH(1,INDEX((('Oncall AMER'!C:C=A2)+('Oncall AMER'!D:D=A2))*(('Oncall AMER'!A:A>=B2)*('Oncall AMER'!A:A<=C2)+('Oncall AMER'!A:A<=B2)*('Oncall AMER'!B:B>=B2)),0),0)))

      I tried using this however getting error of "Too Many Argument". Any suggestion on this as we are adding condition where based on D column value we decide the sheet in which we should search.
      Attached Files

      Comment


      • #4
        Try:

        =IFERROR(IF(D2="OnCall EMEA",ISNUMBER(MATCH(1,INDEX((('Oncall Emea'!C:C=A2)+('Oncall Emea'!D:D=A2))*(('Oncall Emea'!A:A>=B2)*('Oncall Emea'!A:A<=C2)+('Oncall Emea'!A:A<=B2)*('Oncall Emea'!B:B>=B2)),0),0)),IF(D2="OnCall AMER",ISNUMBER(MATCH(1,INDEX((('Oncall AMER'!C:C=A2)+('Oncall AMER'!D:D=A2))*(('Oncall AMER'!A:A>=B2)*('Oncall AMER'!A:A<=C2)+('Oncall AMER'!A:A<=B2)*('Oncall AMER'!B:B>=B2)),0),0)))),"NA")

        you can replace the "NA" with whatever result you want returned if no matches found.
        Where there is a will there are many ways. Finding one that works for you is the challenge!

        MS Excel MVP 2010-2016

        I am participating in the "Walk for a Cure" for the Kidney Foundation of Canada. If you can/want to, please Donate: https://kidney.akaraisin.com/ui/1674...cipant/1145052

        Comment


        • #5
          Thanks mate, it working however Excel seems to be slowed down a lot. I mean processing is too much

          Comment


          • #6


            Don't use whole column references like A:A - limit the arrays to match the size of your data.
            Ali

            Enthusiastic self-taught user of MS Excel who's always learning!
            If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

            Comment

            Working...
            X