[Excel] Lookup a value using a date range

[markh28]

I have the following challenge:

I have a table containing date ranges for weeks, and a week code telling me if this is an A week, a B week, or a C week, like this:

WeekCode FromDate ToDate
A 1/30/2011 2/5/2011
B 2/6/2011 2/12/2011
C 2/13/2011 2/19/2011
D 2/20/2011 2/26/2011
A 2/27/2011 3/5/2011
B 3/6/2011 3/12/2011
C 3/13/2011 3/19/2011
D 3/20/2011 3/26/2011

The user enters in two dates here:
Fromdate= 2/1/2011
ToDate = 2/28/2011

I need the following to be populated:
A WEEK ENDS ON: datethatislookedup
B WEEK ENDS ON: datethatislookedup
C WEEK ENDS ON: datethatislookedup
D WEEK ENDS ON: datethatislookedup

I need to know the formulas that go in the "datethatislookedup" cells
The table of dates I have is for the entire year and is in order.

[westconn1]

as you have multiple weeks of each letter how do we know which A week you want the end date of
what are the input dates for?

[MarMan]

Also where are you getting the dates from? A table in a database? A spreadsheet?
You never specify what datethatislookedup is supposed to do. Find a ToDate that is within a week? Do something with the Fromdate? Or not?
If you want a formula you need to describe clearly and in detail what you need. You know what you want it to do, but unless you explain what you want completely, no one can help you.

[markh28]

The list of dates is in the same sheet and starts at the beginning of the year and go to the end.

The user will enter the from and to dates of the current month. Once they do that, the "datethatislookedup" formula will look between those two entered dates (from and to for the month) and display the week ending date of the A week, the B week,etc.

[MarMan]

Try this:

=IF(C3 < C1, "Week", "")

Where C1 is the ToDate and C3 is the cell with the week ending date. From your example, it would be 2/5/2011.