Returning value from one column based on two neighboring columns

I am attempting to compare a list of dates to a table of date data and am trying to return the value of one of the columns if the date falls between two dates listed in the other columns.

To clarify, I have a column with a list of start dates and a column with a list of end dates (these ranges do not overlap). Next to those two columns I have a third column with a list of data that I wish to return if a given date is between the two dates in the other columns. For example, if a row in column one has 2/1/2014 in it and a row in column two has 2/28/2014 in it, I want to return the data in the third column from that row if I enter in the date 2/26/2014 in it elsewhere.

I have tried creating an array formula to pull the data, but I am not too familiar with them yet and the formula is not working. The formula I have been trying is below:

Please Login or Register  to view this content.

Where A1 is the cell with a given date, WeekStart is a named range with all of the start dates, WeekEnd is a named range with all of the end dates, and Weeks is a named range containing the data in all three columns.

I think that I may need to use ROW() in some fashion, but I am not quite sure how.

I attached an example of what I am working with.

I have tried two different ranges with my formula as you can see. One with all three columns and one with just the first column that has the data.

I also tried the LOOKUP function but that doesn't seem to be working as intended either.

This works well. Thanks for the help.