Lookup date data and return value between 2 date ranges

dahlia94040

New Member
Joined
Oct 19, 2006
Messages
17
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!
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
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
 

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
This is an array formula.
Hold down CTRL and SHIFT keys while you pressing ENTER key.

See help files on array formula.

HTH
 

dahlia94040

New Member
Joined
Oct 19, 2006
Messages
17

ADVERTISEMENT

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

New Member
Joined
Oct 19, 2006
Messages
17
Never mind Kris. Next time I should try it before I ask dumb questions. Thank you soooo much again!
 

Krishnakumar

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
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.
 

Forum statistics

Threads
1,136,512
Messages
5,676,289
Members
419,618
Latest member
Grisego

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top