lookup date in the interval

[Greg]

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 Phillips]

=INDEX(Sheet2!D119,MATCH(1,(A2=Sheet2!A1:A19)*(B2>=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]

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

 

[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.

 

[Bob Phillips]

Tricky on two conditions, you may have to implement helper cells.

You can call my method from VBA

myVar =
Activesheet.Evaluate("INDEX(Sheet2!D119,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)