|
lookup date in the interval
Hi,
My question is best illustrated with example. This is the data I have. String EFFDATE AA 4/15/1999 BB 3/1/1995 CC 1/15/2006 DD 6/4/2002 ...... ........... I am trying to lookup GROUP in the following range, but I have a problem matching my EFFDATE being within the interval of STARTDATE and ENDDATE. Any help is appreciated. String STARTDATE ENDDATE GROUP AA 5/1/94 5/31/94 6 AA 6/1/94 3/31/98 2 AA 4/1/98 6/17/01 29 AA 6/18/01 9/30/03 70 AA 10/1/03 5/18/04 130 AA 5/19/04 3/13/05 237 AA 3/14/05 276 BB 5/1/94 5/31/94 6 BB 6/1/94 8/31/95 2 BB 9/1/95 3/31/97 11 BB 4/1/97 4/30/98 14 BB 5/1/98 9/14/00 33 BB 9/15/00 10/10/01 49 BB 10/11/01 7/1/02 81 BB 7/2/02 11/15/04 109 BB 11/16/04 10/9/05 242 BB 10/10/05 2/1/06 300 BB 2/2/06 301 €¦ €¦ .. €¦ _____ Regards, Greg |
lookup date in the interval
=INDEX(Sheet2!D1:D19,MATCH(1,(A2=Sheet2!A1:A19)*(B 2=Sheet2!B1:B19)*(B2<=She
et2!C1:C19),0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Greg" wrote in message ... Hi, My question is best illustrated with example. This is the data I have. String EFFDATE AA 4/15/1999 BB 3/1/1995 CC 1/15/2006 DD 6/4/2002 ..... ........... I am trying to lookup GROUP in the following range, but I have a problem matching my EFFDATE being within the interval of STARTDATE and ENDDATE. Any help is appreciated. String STARTDATE ENDDATE GROUP AA 5/1/94 5/31/94 6 AA 6/1/94 3/31/98 2 AA 4/1/98 6/17/01 29 AA 6/18/01 9/30/03 70 AA 10/1/03 5/18/04 130 AA 5/19/04 3/13/05 237 AA 3/14/05 276 BB 5/1/94 5/31/94 6 BB 6/1/94 8/31/95 2 BB 9/1/95 3/31/97 11 BB 4/1/97 4/30/98 14 BB 5/1/98 9/14/00 33 BB 9/15/00 10/10/01 49 BB 10/11/01 7/1/02 81 BB 7/2/02 11/15/04 109 BB 11/16/04 10/9/05 242 BB 10/10/05 2/1/06 300 BB 2/2/06 301 . . .. . _____ Regards, Greg |
lookup date in the interval
Bob thank you very much.
I actually was hoping for VBA solution that is why I posted it into this group. My fault, I should have said it. My the ranges are about 4000 rows long and array formulas will slow things down significantly. However, I will use it if nothing else -- ______ Regards, Greg "Bob Phillips" wrote: =INDEX(Sheet2!D1:D19,MATCH(1,(A2=Sheet2!A1:A19)*(B 2=Sheet2!B1:B19)*(B2<=She et2!C1:C19),0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Greg" wrote in message ... Hi, My question is best illustrated with example. This is the data I have. String EFFDATE AA 4/15/1999 BB 3/1/1995 CC 1/15/2006 DD 6/4/2002 ..... ........... I am trying to lookup GROUP in the following range, but I have a problem matching my EFFDATE being within the interval of STARTDATE and ENDDATE. Any help is appreciated. String STARTDATE ENDDATE GROUP AA 5/1/94 5/31/94 6 AA 6/1/94 3/31/98 2 AA 4/1/98 6/17/01 29 AA 6/18/01 9/30/03 70 AA 10/1/03 5/18/04 130 AA 5/19/04 3/13/05 237 AA 3/14/05 276 BB 5/1/94 5/31/94 6 BB 6/1/94 8/31/95 2 BB 9/1/95 3/31/97 11 BB 4/1/97 4/30/98 14 BB 5/1/98 9/14/00 33 BB 9/15/00 10/10/01 49 BB 10/11/01 7/1/02 81 BB 7/2/02 11/15/04 109 BB 11/16/04 10/9/05 242 BB 10/10/05 2/1/06 300 BB 2/2/06 301 . . .. . _____ Regards, Greg |
lookup date in the interval
I think i'm going to use Find & FindNext method, i.e., find the match by
STRING and check if date is in the interval, if not FindNext. -- ______ Regards, Greg "Greg" wrote: Bob thank you very much. I actually was hoping for VBA solution that is why I posted it into this group. My fault, I should have said it. My the ranges are about 4000 rows long and array formulas will slow things down significantly. However, I will use it if nothing else -- ______ Regards, Greg "Bob Phillips" wrote: =INDEX(Sheet2!D1:D19,MATCH(1,(A2=Sheet2!A1:A19)*(B 2=Sheet2!B1:B19)*(B2<=She et2!C1:C19),0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Greg" wrote in message ... Hi, My question is best illustrated with example. This is the data I have. String EFFDATE AA 4/15/1999 BB 3/1/1995 CC 1/15/2006 DD 6/4/2002 ..... ........... I am trying to lookup GROUP in the following range, but I have a problem matching my EFFDATE being within the interval of STARTDATE and ENDDATE. Any help is appreciated. String STARTDATE ENDDATE GROUP AA 5/1/94 5/31/94 6 AA 6/1/94 3/31/98 2 AA 4/1/98 6/17/01 29 AA 6/18/01 9/30/03 70 AA 10/1/03 5/18/04 130 AA 5/19/04 3/13/05 237 AA 3/14/05 276 BB 5/1/94 5/31/94 6 BB 6/1/94 8/31/95 2 BB 9/1/95 3/31/97 11 BB 4/1/97 4/30/98 14 BB 5/1/98 9/14/00 33 BB 9/15/00 10/10/01 49 BB 10/11/01 7/1/02 81 BB 7/2/02 11/15/04 109 BB 11/16/04 10/9/05 242 BB 10/10/05 2/1/06 300 BB 2/2/06 301 . . .. . _____ Regards, Greg |
lookup date in the interval
Tricky on two conditions, you may have to implement helper cells.
You can call my method from VBA myVar = Activesheet.Evaluate("INDEX(Sheet2!D1:D19,MATCH(1, (A2=Sheet2!A1:A19)*(B2=Sh eet2!B1:B19)*(B2<=Sheet2!C1:C19),0))") -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Greg" wrote in message ... I think i'm going to use Find & FindNext method, i.e., find the match by STRING and check if date is in the interval, if not FindNext. -- ______ Regards, Greg "Greg" wrote: Bob thank you very much. I actually was hoping for VBA solution that is why I posted it into this group. My fault, I should have said it. My the ranges are about 4000 rows long and array formulas will slow things down significantly. However, I will use it if nothing else -- ______ Regards, Greg "Bob Phillips" wrote: =INDEX(Sheet2!D1:D19,MATCH(1,(A2=Sheet2!A1:A19)*(B 2=Sheet2!B1:B19)*(B2<=She et2!C1:C19),0)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. Excel will automatically enclose the formula in braces (curly brackets), do not try to do this manually. When editing the formula, it must again be array-entered. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Greg" wrote in message ... Hi, My question is best illustrated with example. This is the data I have. String EFFDATE AA 4/15/1999 BB 3/1/1995 CC 1/15/2006 DD 6/4/2002 ..... ........... I am trying to lookup GROUP in the following range, but I have a problem matching my EFFDATE being within the interval of STARTDATE and ENDDATE. Any help is appreciated. String STARTDATE ENDDATE GROUP AA 5/1/94 5/31/94 6 AA 6/1/94 3/31/98 2 AA 4/1/98 6/17/01 29 AA 6/18/01 9/30/03 70 AA 10/1/03 5/18/04 130 AA 5/19/04 3/13/05 237 AA 3/14/05 276 BB 5/1/94 5/31/94 6 BB 6/1/94 8/31/95 2 BB 9/1/95 3/31/97 11 BB 4/1/97 4/30/98 14 BB 5/1/98 9/14/00 33 BB 9/15/00 10/10/01 49 BB 10/11/01 7/1/02 81 BB 7/2/02 11/15/04 109 BB 11/16/04 10/9/05 242 BB 10/10/05 2/1/06 300 BB 2/2/06 301 . . .. . _____ Regards, Greg |
| All times are GMT +1. The time now is 08:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com