Lookup date data and return value between 2 date ranges

[dahlia94040]

This is my first time posting a question in this forum. I'm desparate for help! I tried searching to see if anyone has asked the same question but I haven't been successful yet.

Sheet 1 contains a transaction table. I need to do a lookup pertaining to that customer & product for that transaction date and have it return a rebate value from sheet two which may not contain that actual date since it only has a start & end date.

Sheet 1 example:

Customer Product Trans.Date Rebate Earned
AAAAAA 111111 01/01/06 ?
BBBBBB 222222 04/15/06 ?
EEEEEE 333333 02/03/06 ?
AAAAAA 333333 01/01/06 ?
EEEEEE 333333 03/01/06 ?

Sheet 2 contains the following:

Customer Product Start Date End Date Rebate
AAAAAA 111111 12/23/05 01/15/06 0.10
AAAAAA 111111 01/16/05 10/26/06 0.05
AAAAAA 222222 01/02/06 12/31/20 0.02
AAAAAA 333333 01/01/05 01/01/06 0.01
AAAAAA 333333 01/02/06 12/31/20 0.03
BBBBBB 222222 04/05/06 04/11/06 0.15
BBBBBB 222222 04/12/06 04/30/06 0.13
EEEEEE 333333 03/04/05 02/15/06 0.55

As you can see from above the start & end dates are not constant. I need the formula to return these rebate values in the last column:

Customer Product Trans.Date Rebate Earned
AAAAAA 111111 01/01/06 0.10
BBBBBB 222222 04/15/06 0.13
EEEEEE 333333 02/03/06 0.55
AAAAAA 333333 01/01/06 0.01
EEEEEE 333333 03/01/06 #N/A

Thank you in advance for your help!

 

[Krishnakumar]

Hi,

=INDEX(Sheet2!$E$2:$E$9,MATCH(1,IF(Sheet2!$A$2:$A$9=A2,IF(Sheet2!$B$2:$B$9=B2,IF(C2>=Sheet2!$C$2:$C$9,IF(C2<=Sheet2!$D$2:$D$9,1)))),0))

Confirmed with Ctrl+Shift+Enter.

HTH

 

[dahlia94040]

Thanks for your quick reply Kris. I tried it and I got #N/A.

 

[Krishnakumar]

This is an array formula.
Hold down CTRL and SHIFT keys while you pressing ENTER key.

See help files on array formula.

HTH

 

[dahlia94040]

I thought I did that but I clicked on the formula and held down CTRL and SHIFT while pressing ENTER again and it worked! Thank you!

If I need to copy that formula down (32 thousand rows) do I have to do that each time.

 

[dahlia94040]

Never mind Kris. Next time I should try it before I ask dumb questions. Thank you soooo much again!

 

[Krishnakumar]

IIf I need to copy that formula down (32 thousand rows) do I have to do that each time.

Using array formulas in 32k rows will affect the performance of the system. Consider VBA.