Lookup a date between other dates

[spalmarez]

I have a dilema. I am trying to grab a date. Here are my columns, but
have more than this:
START END START_1 END_1 START_2 END_2
23-Dec-04 17-Jan-05 25-Jan-05 15-Feb-05 23-Feb-05 16-Mar-05

Basically, I enter a date I want to look up and get the value, whic
works for a few columns but not for 26 columns.

Here is my formula:
A4= 01/26/05 or any date


=IF(AND(A4>=F4,A4<G4),"A",IF(AND(A4>=H4,A4<I4),"B",IF(AND(A4>=J4,A4<K4),"C",IF(AND(A4>=L4,A4<M4),"D",IF(AND(A4>=N4,A4<O4),"E",IF(AND(A4>=P4,A4<Q4),"F",IF(AND(A4>=R4,A4<S4),"G")))))))

But I get an error when I enter more in. Could anyone provide feedbac
on this.
Basically I am looking for The Start date and End date between m
value. Any help would be appreciated or idea

 

[Guest]

You are trying to add more IF Statements? The limit is Seven. After that
Excel will always return an error. You may want to Explore some other
functions.
VLOOKUP, INDEX, MATCH, OFFSET can sometimes be used to solve problems like
the one you are presenting, but you may have to redesign the worksheet.

tj

 

[Myrna Larson]

I don't think you will be able to used any of the built-in lookup functions
with this layout. You want to return the largest value that is <= your target
value. To do that requires that the data be sorted in ascending order. No way
to do that with what you have. You should redo the worksheet so the ending day
is in the row below the starting date. If there's some reason you can't do
that, then you'll have to implement a VBA macro function, which will be
significantly slower to recalculate than a worksheet function.

 

[Domenic]

Try the following array formula, entered using CONTROL+SHIFT+ENTER...

=INDEX(Sheet2!A1:A13,MATCH(1,(A4>=SUBTOTAL(9,OFFSET(F4,0,ROW(INDIRECT("1:13"))*2-2)))*(A4<SUBTOTAL(9,OFFSET(G4,0,ROW(INDIRECT("1:13"))*2-2))),0))

...where Sheet2!A1:A13 contain letters A through M.

Hope this helps!

 

[Avner]

Look also in Pearson's

http://www.cpearson.com/excel/DateIntervals.htm

and his other date formulas

avne