|
Lookup a date between other dates
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 |
| All times are GMT +1. The time now is 05:54 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com