Need formula to return next Monday's date

[Frustrated in Portland]

I'm trying to return the mm/dd/yyyy for the next Monday on a continuing
basis. On a separate sheet I created column "A" with =TODAY() in cell A2
and filled down a series of the next six days. B2:B8 cells equal the
corresponding values from A2:A8 formated as dddd to display a continuously
rotating list of the days of the week.

I want to look up the text "Monday" from column B and return the
corresponding date value from column A. How can I use TEXT() in the
=INDEX(Sheet1!A2:B8,MATCH("Monday",Sheet1!B2:B8,0),2) function to make this
work? I'm getting #N/A.

Is there an easier way?

I'm using Excel 2007

Thanks, Frustrated in Portland

 

[Tim Zych]

=TEXT(INDEX(Sheet1!A2:B8,MATCH("Monday",Sheet1!B2:B8,0),1),"mm/dd/yyyy")

 

[T. Valko]

Is there an easier way?

So, if today is Monday then you still want next Monday's date?

=TODAY()-WEEKDAY(NOW(),2)+8

 

[Frustrated in Portland]

Thanks for the helpful replies.

It works!

"No longer frustrated in Portland"

 

[T. Valko]

You're welcome. Thanks for the feedback!