# Lookup date data and return value between 2 date ranges

#### dahlia94040

##### New Member
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

### 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
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

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

#### Krishnakumar

##### Well-known Member
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

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
Never mind Kris. Next time I should try it before I ask dumb questions. Thank you soooo much again!

#### Krishnakumar

##### Well-known Member
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.

Replies
5
Views
35
Replies
5
Views
180
Replies
2
Views
94
Replies
11
Views
139
Replies
4
Views
65

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.

### Which adblocker are you using?

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

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