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

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.

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

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.

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?

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"

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

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

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!