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