Short Formula To Pull Upcoming Holidays For a Title Merge In A Huge Database

[SSElvis]

I am Using excel 2007 and running Windows 7 Pro. I have 50 Thousand rows with data.

I am trying to do a merge of different cells to create a title, One of the cells that I need to merge is an upcoming holiday. I have 4 columns of holidays.
For example: Today's date is 5/22/2015 next upcoming holiday would be Memorial Day and after that would be Father's Day.
So, from Today's date to Memorial day the title would contain "Memorial Day" After memorial day, I want the formula to automatically replace "Memorial day" with "Father's Day" And doing the same to upcoming holidays after that.
I have researched forums and gotten ideas to compose a formula. I was able to create one, but the only way I got it to work was with the holiday's date instead of there name. If there is no other way of doing it besides that way I would be okay with it.
Only thing i'm concerned about is Opening, Saving, and Editing a file that large with a formula that big is very time consuming and un-responsive sometimes. I need help and would like to have a formula that dose what I need more efficiently and quicker. ideas on shortening the formula

Here is my formula:=IF(SMALL(IF($A$2:$A$7>TODAY(),$A$2:$A$7),1)=DATE(YEAR(TODAY()),2,14),"Valentine's Day",IF(SMALL(IF($A$2:$A$7>TODAY(),$A$2:$A$7),1)=DATE(YEAR(TODAY()),4,5),"Easter Day",IF(SMALL(IF($A$2:$A$7>TODAY(),$A$2:$A$7),1)=DATE(YEAR(TODAY()),5,10),"Mother's Day",IF(SMALL(IF($A$2:$A$7>TODAY(),$A$2:$A$7),1)=DATE(YEAR(TODAY()),6,21),"Father's Day",IF(SMALL(IF($A$2:$A$7>TODAY(),$A$2:$A$7),1)=DATE(YEAR(TODAY()),10,31),"Halloween",IF(SMALL(IF($A$2:$A$7>TODAY(),$A$2:$A$7),1)=DATE(YEAR(TODAY()),12,25),"Christmas"))))))

I have dates in Array A2:a7
Thanks in advance for any help, tips, and ideas.

 

[MarcelBeug]

This formula is much shorter. It's an array formula that needs to be confirmed with CTRL-SHIFT-ENTER.

=INDEX({"Valentine's Day";"Easter Day";"Mother's Day";"Father's Day";"Halloween";"Christmas",""},1+SUM(--((A2:A7)<=TODAY())))

After confirming with CTRL-SHIFT-ENTER the formula will be automatically surrounded by curly brackets {}.

 

[AlphaFrog]

I am Using excel 2007 and running Windows 7 Pro. I have 50 Thousand rows with data.

I am trying to do a merge of different cells to create a title, One of the cells that I need to merge is an upcoming holiday. I have 4 columns of holidays.

You want to repeat this formula for 50 thousand rows and return the same result 50 thousand times? If yes, it would be more efficient to calculate the holiday once, and then just repeat the result for the other rows. So if the formula was in B1, put this in B2 =$B$1 and copy down column B to B50000.

 

[SSElvis]

This formula is much shorter. It's an array formula that needs to be confirmed with CTRL-SHIFT-ENTER.

=INDEX({"Valentine's Day";"Easter Day";"Mother's Day";"Father's Day";"Halloween";"Christmas",""},1+SUM(--((A2:A7)<=TODAY())))

After confirming with CTRL-SHIFT-ENTER the formula will be automatically surrounded by curly brackets {}.

Thank you for your feedback!
I implemented this formula to my worksheet, unfortunately it wasn't able to update once the dates changed. It always gave me "Valentine's Day", i'm guessing because that one is first on the formula.?

i'm looking for the values to change in between given dates or x amount of days before an upcoming holiday.

 

[MarcelBeug]

Ai, I see I used ";" instead of ","as a delimiter between the holidays (except for the last one, which is ",").
The range A2:A7 in my formula is not fixed, so if you copy the formula you should fix the range ($A$2:$A$7).
This should be better:

=INDEX({"Valentine's Day","Easter Day","Mother's Day","Father's Day","Halloween","Christmas",""},1+SUM(--(($A$2:$A$7)<=TODAY())))

Make sure you finish editing the formula with CTRL-SHIFT-ENTER, not just ENTER.

Apologies for my mistakes (here in Europe we use ";" as a delimiter instead of "," so I need to adjust it every time when posting a formula).

Hope this will work for you now.