How to automatically update discount based on date

[okvals48]

Hi,

i would like to update discount values automatically in my table based on the date in my master data table.
So for example i have a table where i have discounts but this discounts are only valid for certain period of time, after that end date i enter new discounts and i would like that new discounts are applied from the new date, but i also need that old ones do get affected by my change.

Thank you for your help

 

[etaf]

in customer 1 you have a discount valid until 14th May of 2% , BUT customer 1 also has a discount of 2.2% on 1st April, so there is an overlap

 

[etaf]

Assuming NO overlap in dates for same customer

I think this should work, if it finds a valid date it will return the discount or error will be 0%
=IFERROR(LOOKUP(1E+307,1/((C3=I:I)*(D3>=J:J)*(C3<=K:K))*L:L),0)

But where you have blank cells they may be seen as 1/1/1900 - so it maybe worth adding a future date and then changing when you stop the promotion with the date the promotion stops
OR add today() - so it sees today as part of the end date and change that when reach the end date

lookup ID value and date between range of dates.xlsx
	A	B	C	D	E	F	G	H	I
1	Customer	Date purchased	Rebate Due			Customer	Start	End	Percent
2	3	7/10/16	0.0%			1	5/1/16	5/11/16	1%
3	4	7/11/16	0.0%			1	1/1/17	1/11/17	2%
4	5	7/12/16	0.0%			2	6/1/16	6/11/16	3%
5	6	10/10/16	11.0%			2	2/1/17	2/11/17	4%
6	7	10/8/16	0.0%			3	7/1/16	7/9/16	5%
7	8	12/8/16	15.0%			3	3/1/17	3/11/17	6%
8	9	3/6/17	18.0%			4	8/1/16	8/11/16	7%
9	9	1/6/17	17.0%			4	4/1/17	4/11/17	8%
10						5	9/1/16	9/11/16	9%
11						5	5/1/17	5/11/17	10%
12						6	10/1/16	10/11/16	11%
13						6	6/1/17	6/11/17	12%
14						7	11/1/16	11/11/16	13%
15						7	1/1/17	1/11/17	14%
16						8	12/1/16	12/11/16	15%
17						8	2/1/17	2/11/17	16%
18						9	1/1/17	1/11/17	17%
19						9	3/1/17	3/11/17	18%
20						10	2/1/17	2/11/17	19%
21						10	4/1/17	4/11/17	20%
22						11	7/1/16	7/11/16	21%
Sheet1

Cell Formulas
Range	Formula
C2:C9	C2	=IFERROR(LOOKUP(1E+307,1/((A2=F:F)*(B2>=G:G)*(B2<=H:H))*I:I),0)

 

[okvals48]

Thanks for replay,

i have few more questions:
1. i do not understand this part marked in red, what does it do, how is it related with lookup value
IFERROR(LOOKUP(1E+307,1/((A2=F:F)*(B2>=G:G)*(B2<=H:H))*I:I),0)

2.and why did we divided lookup vector with 1

 

[etaf]

have a read here

9.9999999 .... ?

Hey there I see this 9.99999999999.. to be used now and then in formulas like =IF((Sheet2!D2"")*(Sheet2!D2=$A$1),LOOKUP(9.99999999999999E+307,$B$1:B1)+1,"") What is the function of that. I know it works in those cases I have seen but I would like to learn how to use it and why? Regards...

www.mrexcel.com