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

1. ## 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

2. ## 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:
`Please Login or Register  to view this content.`

3. ## 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. ## 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.

5. ## 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.

6. ## 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. ## 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. ## 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.

9. ## 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. ## 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. ## 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.

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