Return value from column A if >=column B and <=column C

Greetings,

I have the following data stored in worksheet #1 called 'Validation' which I want to use in my formula. "Iteration" Title is Column 1, Row 1.

Iteration Start Date End Date
1 1/1/2010 2/6/2010
2 2/7/2010 2/27/2010
3 2/28/2010 3/20/2010

In worksheet #2, I am using the following formula in G12:
=IF(AND(G11>=Validation!B2,G11<=Validation!C2),Validation!A2,"")
This formula is working fine if my date range in cell G12 falls between 1/1/2010 and 2/6/2010. The cell gets properly filled with 1.

What I want to do though is check ALL rows (date ranges) in the Validation worksheet. So I want to return 1 if the range is met for row 1, but I want to return 2 if the range is met for row 2, etc. Other than writing a very long formula that repeats what I have for every row, I'm not sure if there is an easier way to accomplish this. Do I need a Lookup? I'm not sure what to do here.

Thanks for the help!
Hopefully I've made sense here.

Drew

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Removed unnecessary wording

If the date you are seeking is in cell A2, then the following formula will look at the date and give you the iteration number:

=INDEX(Validation!A2:A4,MATCH(A2,Validation!B2:B4,1))

HOWEVER I've made an important assumption based on your sample data. My assumption is that the dates for the iterations are strictly ascending, and that the start date is always 1 day later than the preceding iteration's end date. If either of these assumptions is false then my solution will probably not work, but a solution for other situations is possible.

Originally Posted by dwiseman

Thanks but I don't think this is getting me what I need. Your assumpion about contiguous days is correct. The problem though is that I am entering a date in cell G12, and I want to check that date against a date range from the validation worksheet: (start date column B, end date column C) and based on which row is a match, return the corresponding row's column A which is the iteration number.

That is exactly what this formula does. If my assumption is true then you actually never need to look at column C. I have attached a prototype and you can experiment to see if this meets your needs. Note that the table is in Validation and the date/lookup is in Sheet2.

PS Berklee College of Music grad...'89

What's your instrument? I have met lots of Berklee alums over the years. I studied out of William Leavitt's books for a while.