VLOOKUP, DATE, TODAY, and IF - Help Needed

[coffeechat]

I have a column with all the Sundays for the year 2014. However, in another column, I need to eliminate the Sundays that have already gone past the TODAY date so I can use this to create a list using Data Validation. I have tried all formulas that I can think of based on this logic but I am rather unsuccessful.

Any help would be much appreciated.

Thank you.

 

[FDibbins]

How about using a helper column? If your sunday (I presume you mean dates?) are in column A, put =TODAY() in a cell somewhere - say C1), then in column B, copied down, something like this...

=IF(A1<$C$1,"",A1)

 

[coffeechat]

Hi Ford,

Thank you for your response. I have that function in another column previously but what I want is something more complex. Perhaps I need to explain it better.

So, if the date in A1 is less than C1, I want it to go to A2, and so on, until it finds the first cell with a date that is greater than the TODAY date.

Hope this makes sense.

 

[AhoyNC]

Maybe something like INDEX / MATCH.
This is an array formula and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C
1	Sunday, May 04, 2014		7/27/2014
2	Sunday, May 11, 2014
3	Sunday, May 18, 2014
4	Sunday, May 25, 2014
5	Sunday, June 01, 2014
6	Sunday, June 08, 2014
7	Sunday, June 15, 2014
8	Sunday, June 22, 2014
9	Sunday, June 29, 2014
10	Sunday, July 06, 2014
11	Sunday, July 13, 2014
12	Sunday, July 20, 2014
13	Sunday, July 27, 2014
14	Sunday, August 03, 2014
15	Sunday, August 10, 2014
16	Sunday, August 17, 2014
17	Sunday, August 24, 2014
Sheet

 

[coffeechat]

Hi AhoyNC.

Many thanks for this! It works!

Problem solved, now I can get on with my day.

Cheers

 

[AhoyNC]

You're welcome. Thanks for the feedback and welcome to the form.