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

=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

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

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

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