+ Reply to Thread
Results 1 to 11 of 11

How to Lookup and match a day between 2 dates and return the holiday name

  1. #1
    Registered User
    Join Date
    12-17-2020
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    34

    Question How to Lookup and match a day between 2 dates and return the holiday name

    Hello,

    I'm trying to find a way to look a holiday if it falls between two dates, and return the holiday name in another cell.

    I'm attaching the excel file, thanks for help if anyone have suggestions.


    Thanks
    Attached Files Attached Files

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,174

    Re: How to Lookup and match a day between 2 dates and return the holiday name

    This formula does what you specified but do you really just want to check a single holiday against a single pair of dates? Or do you want to match a whole list of holidays?

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    12-17-2020
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    34

    Re: How to Lookup and match a day between 2 dates and return the holiday name

    Thanks a lot, Actually NO, I wanted to look into a list of holidays for sure, I just included the excel, with one row, I might have needed to explain more

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    mysore
    MS-Off Ver
    Excel 2010
    Posts
    5,492

    Re: How to Lookup and match a day between 2 dates and return the holiday name

    Holiday list is available in other workbook. So upload that also.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  5. #5
    Registered User
    Join Date
    12-17-2020
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    34

    Question Re: How to Lookup and match a day between 2 dates and return the holiday name

    Ok, for a better explanation of the solution I'm searching for, I have attached the right workbook with the holidays list dates and names

    So what I need to find out is:

    If a Holiday Day falls between start date C and End date D, then show the Holiday name in A and relative date in B

    Holidays list and names are in Sheet "Holidays" and named holiday_dates & holiday_names


    Thank you all, for helping.
    Attached Files Attached Files

  6. #6
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,174

    Re: How to Lookup and match a day between 2 dates and return the holiday name

    For many of these ranges there are multiple holidays. For example, in row 3 there are 14 holidays in that range. How do you want to handle that?

  7. #7
    Registered User
    Join Date
    12-17-2020
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    34

    Re: How to Lookup and match a day between 2 dates and return the holiday name

    Yes that's another challenge, maybe to have the start and end dates of this range or just the 1st date in this range! That's so challenging.
    Or maybe if there are many, then return "Long Holiday"

  8. #8
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    17,174

    Re: How to Lookup and match a day between 2 dates and return the holiday name

    This version shows the count of holidays in the range and the date and name of the first one.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    12-17-2020
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    34

    Re: How to Lookup and match a day between 2 dates and return the holiday name

    OMG! That's Amazing! Thanks a lot 6StringJazzer, That solves the issue, by adding Holidays count, I was struggling to find all this, I will learn how it works. Happy Holidays

  10. #10
    Registered User
    Join Date
    12-17-2020
    Location
    Canada
    MS-Off Ver
    2010
    Posts
    34

    Re: How to Lookup and match a day between 2 dates and return the holiday name

    Any way to exclude those holidays named EXTRA from being seen by excel? Using VBA I can hide or show them but they are still there, just hidden!
    I'm trying to find a way to exclude those days from the list, but show them again if needed!


  11. #11
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,537

    Re: How to Lookup and match a day between 2 dates and return the holiday name

    Perhaps the following will help.
    1. Place the word EXTRA in cell G1
    2. Modify the formula in the holiday count column to read: =COUNTIFS(Holidays!holiday_dates,">="&$C2,Holidays!holiday_dates,"<="&$D2,holiday_names,"<>"&G$1)
    3. Modify the formula in the holiday date column to read: =IF(E2>0,INDEX(Holidays!holiday_dates,AGGREGATE(15,6,(ROW(holiday_names)-ROW(A$1))/(holiday_names<>G$1)/(Holidays!holiday_dates>=C2),1)),"")
    4. [Optional] Modify the formula in the holiday name column to read: =IF(B2="","",INDEX(holiday_names,MATCH(Sheet5!B2,Holidays!holiday_dates,0)))
    Note that when the word EXTRA is deleted from cell G1 it will again be shown in the holiday name column.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Excel lookup between 2 Dates if Holiday Occurs
    By revocats10 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 11-13-2020, 12:35 AM
  2. Two-way lookup with INDEX and MATCH return closest match
    By Mschelle6 in forum Excel General
    Replies: 8
    Last Post: 12-06-2018, 02:03 PM
  3. [SOLVED] Lookup 2 different dates and return a horizontal lookup
    By pmw19800 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-16-2018, 05:26 AM
  4. working on a holiday planner based on various holiday anniversary dates
    By marktc19 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-23-2017, 09:26 AM
  5. Lookup Date in Table of Holiday Dates
    By ExcelSponge in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-14-2015, 03:51 PM
  6. Work out diffrence between 2 dates minus a couple of holiday range dates
    By wogboy112 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-20-2015, 07:04 AM
  7. [SOLVED] IF function using WORKDAYS and a Bank Holiday list - trying to add new holiday dates
    By jowarks in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-05-2013, 05:42 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1