If a date falls between two week ending days, return the holiday associated with that day.

[dragoenix]

Hello,

First, thank you so much for looking at this post and assisting me.

Secondly, I have an Excel Worksheet where I have a list of Week Ending Dates. On the side, I have a list of Holidays and their dates. I would like to have Excel return which Holiday falls between the Week Ending Dates in the cell next to it.

Attached is the file.

Any help would be appreciated. The end goal is to just have an input area for the holidays so I can just change the dates and Excel puts the appropriate Holiday into the corresponding neighboring cell.

[Bernie Deitrick]

Try this formula in D36, copied down to match your 2020 dates:

=IFERROR(VLOOKUP(R367,W:X,2,FALSE),IF(COUNTIFS(W:W,">"&R366,W:W,"<"&R367)>0,VLOOKUP(R367,W:X,2,TRUE),""))

[dragoenix]

Bernie,

Thank you. That did the trick.

Can you explain the logic behind this formula?

Thank you again.

[Bernie Deitrick]

If the Sunday date (R367) is in your list (EXACTLY - the False) then return the holiday. If it is not in your list and there is a date in your list within the last week (the COUNTIFS part) then find that date (the TRUE part of the VLOOKUP finds the latest date in a sorted list that is not greater than the date used as the match) and return the holiday associated with the earlier date.