I have a dilema. I am trying to grab a date. Here are my columns, but I
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, which
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 feedback
on this.
Basically I am looking for The Start date and End date between my
value. Any help would be appreciated or idea.


--
spalmarez
------------------------------------------------------------------------
spalmarez's Profile: http://www.excelforum.com/member.php...o&userid=15961
View this thread: http://www.excelforum.com/showthread...hreadid=274366

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

"spalmarez" wrote:


I have a dilema. I am trying to grab a date. Here are my columns, but I
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, which
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 feedback
on this.
Basically I am looking for The Start date and End date between my
value. Any help would be appreciated or idea.


--
spalmarez
------------------------------------------------------------------------
spalmarez's Profile: http://www.excelforum.com/member.php...o&userid=15961
View this thread: http://www.excelforum.com/showthread...hreadid=274366

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.

On Mon, 1 Nov 2004 16:46:33 -0600, spalmarez
wrote:


I have a dilema. I am trying to grab a date. Here are my columns, but I
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, which
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 feedback
on this.
Basically I am looking for The Start date and End date between my
value. Any help would be appreciated or idea.

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

=INDEX(Sheet2!A1:A13,MATCH(1,(A4=SUBTOTAL(9,OFFSE T(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!


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=274366

Look also in Pearson's

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

and his other date formulas

avner


--
Avner
------------------------------------------------------------------------
Avner's Profile: http://www.excelforum.com/member.php...fo&userid=5078
View this thread: http://www.excelforum.com/showthread...hreadid=274366