Need a formula that checks date, & returns holiday name or name of location based on day

I am trying to make a formula for a cell that checks whether or not the date next to it is a holiday...If true, it outputs the holiday name. If false, it outputs a location name based on the day of the week. I have been able to do each of these separately (holiday output and location output), but can't think of how to do it together. On days that are a holiday, I don't need the location.

I have a calendar with all the dates automatically filled in (column A). It also changes the color for weekends, and a different color for holidays. I used nested if statements to have it check my list of holidays (in Japan), and return the name of the holidays in the next column (column B) for days that are holidays. What I would like it to do, is to write a location name in column B, for days that are not holidays, based on the day of the week. So, if it is a holiday, the holiday name would appear. If not, a location name would appear, based on the day of the week (Monday & Tuesday: LOCATION 1, Wednesday & Thursday: LOCATION 2, FRIDAY: LOCATION 3, Sat & Sun: blank).

I can do this using nested IF statements for either the holiday check or the weekday check, but not together. There is probably an easier way to do this than nested IF statements, but I can't figure it out. If there was an ELSE statement in Excel, I would use the formula "(nested IF statements to check for holidays) ELSE (nested IF statements to fill in locations).

Try the below in cell B4 and copy down for one way of accomplishing this:

Formula:

Please Login or Register  to view this content.

BSB

Or:

Formula:

Please Login or Register  to view this content.

BSB

Wow!

Both formulas work perfectly. Much more elegant than the long nested IF statement I was using just for filling in the holidays. I haven't used IFERROR or VLOOKUP before, although I looked into VLOOKUP when I was trying to figure this out. I haven't used IF(OR..) statements yet either, but I'm going to spend some time figuring out what you did.

Thank you so much for helping me with this. I still have a long way to go in learning Excel, and I really appreciate you taking the time to help me with this.

Glad I could help.

Have fun studying how it all works and feel free to ask all the questions you need if it doesn't make sense. That's what we're here for.

BSB

This formula is awesome. I am trying to apply it to conditional formatting in order to type in the cell if necessary but I am having no luck. Suggestions?

I would suggest starting a new thread (you can reference this thread by linking to it). Attach a sample workbook to your new thread so we can seethe issue in context and explain as fully as you can what your desired output is.

BSB