Need a code for date checking

What if C9 is greater than more than one value in range Setup!F18:F41?
What if C9 is not greater than any values in range Setup!F18:F41?
Can there be any blank values in range Setup!F18:F41?
If so, how are they to be handled?

If the c9 is greater than more than one value, it would be the most recent.
Let's say F18 = 02/04/12 and F19 = 03/07/12 and C9 = 03/09/12 than E20 would be the cell we want.

If c9 is not greater than any within range I would want a message box to appear and then I would send the user the the 'settings' sheet.

There should not be any blank values, if So a message box and sending the user to the settings sheet.

To give a little clarity: here is an example of my range:
E F
18 Period 1 2/6/2012
19 Period 2 3/5/2012
20 Period 3 4/2/2012
21 Period 4 5/7/2012
22 Period 5 6/4/2012
23 Period 6 7/2/2012
24 Period 7 8/6/2012
25 Period 8 9/3/2012
26 Period 9 10/1/2012
27 Period 10 11/5/2012
28 Period 11 12/3/2012
29 Period 12 12/31/2012
These are the ending date for accounting periods. This first part is 2012. So if C9 is a date between 3/5/12 and 4/2/12 then it would be period 3. In other words Period 3 runs from 3/6/12 - 4/1/12. I figured it would be easier to just look at what it is higher than and do the appropriate cell in relation to the date, but if you have a different idea that might be better/easier I'm all for it.

Hello Andrew,

Here is an array formula you can put into cell 'Daily'H9. After you copy the formula into the cell, use Ctrl+Shift+Enter to confirm it. After you do this, you will see the formula enclosed in braces { }.

=INDEX('Setup'!$E$18:$E$40,MATCH(1,--(C9>='Setup'!F18:F40)*--(C9<'Setup'!F19:F41),0),1)

The formula seems to work but it appears to be off.

with C9 = 3/27/2012 my formula in H9 should return a value of 'Period 3'. Instead it is returning 'Period 2'. I tried other dates and it seems to be behind by one period (or one cell in column E)

If you just need to move one cell down from what Leith's formula returns, just use the Offset function, i.e.
(using Ctrl+Shift+Enter to confirm)</Setup!F19:F41),0),1),1,0)<>

awesome! thank you!!!

Good Stuff, I just used this. The only thing that I changed is the sign on the first matrices

=INDEX($B$9:$F$82,MATCH(1,--(H7<=$C$9:$C$80)*--(H7<$C$35:$C$81),0),5)


It took me a bit to find it, but it should be noted that there is an offset to the ranges.

(H7<=$C$9:$C$80)*--(H7<$C$10:$C$81)

It also took me a bit to understand why the match was = "1" and it was a slick way of making things ="True"