Dynamic range with Choose and match

[okvals48]

Hi,

i have created daynamic range in my formula using Choose and Match, and i was wondering is there maybe a better aproach then one I have implemented.

+LOOKUP(1E+307;1/(($C17=$K$4:$K$24)*($D17>=$L$4:$L$24)*($D17<=$M$4:$M$24))*CHOOSE(MATCH(H$15;$N$3:$Q$3);$N$4:$N$24;$O$4:$O$24;$P$4:$P$24;$Q$4:$Q$24))
With match i get relative number to which my search is related to, so choose knows in what column he should look for data.

One question how can i refine this, and i have a problem because match does not match headers in tables but when I insert numbers for example 1 to 4 everything works normaly.

Thanks in advance

Automatic discount update based on time period.xlsx
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
3	Customer	Date purchased	Discount						Customer	Start	End	DISCOUNT	REBATE	OFF REBATE	DATA EX
4	3	10.7.2016	2,00						1	5.1.2016	5.11.2016	2	8	15	4
5	4	11.7.2016	2						1	1.1.2017	1.11.2017	2	8	15	4
6	5	12.7.2016	2						2	6.1.2016	6.11.2016	2	8	15	4
7	6	10.10.2016	2						2	2.1.2017	2.11.2017	2	8	15	4
8	7	8.10.2016	2						3	7.1.2016	7.9.2016	2	8	15	4
9	8	8.12.2016	No disocunt for this period						3	3.1.2017	3.11.2017	2	8	15	4
10	9	6.3.2017	2						4	8.1.2016	8.11.2016	2	8	15	4
11	9	1.6.2017	2						4	4.1.2017	4.11.2017	2	8	15	4
12	7	15.10.2017	2						5	9.1.2016	9.11.2016	2	8	15	4
13									5	5.1.2017	5.11.2017	2	8	15	4
14									6	10.1.2016	10.11.2016	2	8	15	4
15	CUSTOMER	DATE PURCHASED	DISCOUNT	REBATE	OFF REBATE	DATA EX			6	6.1.2017	6.11.2017	2	8	15	4
16	3	10.7.2016	2	8	2	#N/A			7	1.1.2016	31.12.2016	2	8	15	4
17	4	11.7.2016	2	8	2	#N/A			7	1.1.2017	31.12.2017	2	8	15	4
18	5	12.7.2016	2	8	2	#N/A			8	12.1.2016	12.11.2016	2	8	15	4
19	6	10.10.2016	2	8	2	#N/A			8	2.1.2017	2.11.2017	2	8	15	4
20	7	8.10.2016	2	8	2	#N/A			9	1.1.2017	30.5.2017	2	8	15	4
21	8	8.12.2016	#N/A	#N/A	#N/A	#N/A			9	1.6.2017	3.11.2017	2	8	15	4
22	9	6.3.2017	2	8	2	#N/A			10	2.1.2017	2.11.2017	2	8	15	4
23	9	1.6.2017	2	8	2	#N/A			10	3.11.2017	31.12.2017	2	8	15	4
24	7	15.10.2017	2	8	2	#N/A			11	7.1.2016	7.11.2016	2	8	15	4
25	10	2.11.2017	2	8	2	#N/A
26	11	7.1.2016	2	8	2	#N/A
3

Cell Formulas
Range	Formula
E4:E8,E10:E12	E4	=+IFERROR(LOOKUP(1E+307,1/((C4=K4:K24)*(D4>=L4:L24)*(D4<=M4:M24))*N4:N24),"No disocuntfor this period")
E9	E9	=+IFERROR(LOOKUP(1E+307,1/((C9=K9:K29)*(D9>=L9:L29)*(D9<=M9:M29))*N9:N29),"No disocunt for this period")
E16:H26	E16	=+LOOKUP(1E+307,1/(($C16=$K$4:$K$24)*($D16>=$L$4:$L$24)*($D16<=$M$4:$M$24))*CHOOSE(MATCH(E$15,$N$3:$Q$3),$N$4:$N$24,$O$4:$O$24,$P$4:$P$24,$Q$4:$Q$24))

 

[Fluff]

Your match function is doing an approximate match, it should be

MATCH(E$15,$N$3:$Q$3,0)

 

[Fluff]

As you have xl365, you could also use

+Fluff 1.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
3	Customer	Date purchased	Discount						Customer	Start	End	DISCOUNT	REBATE	OFF REBATE	DATA EX
4	3	10/07/2016	2						1	05/01/2016	05/11/2016	2	8	15	4
5	4	11/07/2016	2						1	01/01/2017	01/11/2017	2	8	15	4
6	5	12/07/2016	2						2	06/01/2016	06/11/2016	2	8	15	4
7	6	10/10/2016	2						2	02/01/2017	02/11/2017	2	8	15	4
8	7	08/10/2016	2						3	07/01/2016	07/09/2016	2	8	15	4
9	8	08/12/2016	No disocunt for this period						3	03/01/2017	03/11/2017	2	8	15	4
10	9	06/03/2017	2						4	08/01/2016	08/11/2016	2	8	15	4
11	9	01/06/2017	2						4	04/01/2017	04/11/2017	2	8	15	4
12	7	15/10/2017	2						5	09/01/2016	09/11/2016	2	8	15	4
13									5	05/01/2017	05/11/2017	2	8	15	4
14									6	10/01/2016	10/11/2016	2	8	15	4
15	CUSTOMER	DATE PURCHASED	DISCOUNT	REBATE	OFF REBATE	DATA EX			6	06/01/2017	06/11/2017	2	8	15	4
16	3	10/07/2016	2	8	15	4			7	01/01/2016	31/12/2016	2	8	15	4
17	4	11/07/2016	2	8	15	4			7	01/01/2017	31/12/2017	2	8	15	4
18	5	12/07/2016	2	8	15	4			8	12/01/2016	12/11/2016	2	8	15	4
19	6	10/10/2016	2	8	15	4			8	02/01/2017	02/11/2017	2	8	15	4
20	7	08/10/2016	2	8	15	4			9	01/01/2017	30/05/2017	2	8	15	4
21	8	08/12/2016	No Match						9	01/06/2017	03/11/2017	2	8	15	4
22	9	06/03/2017	2	8	15	4			10	02/01/2017	02/11/2017	2	8	15	4
23	9	01/06/2017	2	8	15	4			10	03/11/2017	31/12/2017	2	8	15	4
24	7	15/10/2017	2	8	15	4			11	07/01/2016	07/11/2016	2	8	15	4
25	10	02/11/2017	2	8	15	4
26	11	07/01/2016	2	8	15	4
Data

Cell Formulas
Range	Formula
E4:E8,E10:E12	E4	=+IFERROR(LOOKUP(1E+307,1/((C4=K4:K24)*(D4>=L4:L24)*(D4<=M4:M24))*N4:N24),"No disocuntfor this period")
E9	E9	=+IFERROR(LOOKUP(1E+307,1/((C9=K9:K29)*(D9>=L9:L29)*(D9<=M9:M29))*N9:N29),"No disocunt for this period")
E22:H26,E21,E16:H20	E16	=FILTER(FILTER($N$4:$Q$24,($C16=$K$4:$K$24)*($D16>=$L$4:$L$24)*($D16<=$M$4:$M$24),"No Match"),MATCH(E$15,$N$3:$Q$3,0),"No match")
Dynamic array formulas.

 

[okvals48]

Thank you

 

[Fluff]

You're welcome & thanks for the feedback.