Dynamic Filter Formula

[irfman]

Hi, I need your help with a dynamic filter formula based on the values in cells AE2 (Type) and AF2 (Date). The filter should return values that match the selection criteria both horizontally and vertically, with the output displayed in cell A1. I'm not sure if this is possible, but I would appreciate your support.

Regards

 

[Sam_D_Ben]

Something like this,

Book1
	A	B	C	D	E	F	G
1	A	B	U	Y		AE	AF
2	Type	CITY	23-Dec	24-Dec		Type	Date
3	Region 1	Newyork	356,185,131	585,268,748		Region 1	23-Dec
4	Region 1	London	261,195,673	31,808,836
5	Region 1	Oslo	56,072,599	49,343,517
6	Region 1	Sydney	117,926,597	87,492,786
7	Region 2	Berlin	25,880,269	26,256,754
8	Region 2	Madrid	11,236,121	11,114,443
9	Region 2	Mumbai	7,061,843	6,973,618
10	Region 2	Tokyo	14,568,312	18,688,591
11
12
13
14	Answer
15	Region 1	Newyork	356,185,131
16	Region 1	London	261,195,673
17	Region 1	Oslo	56,072,599
18	Region 1	Sydney	117,926,597
19
20	Region 1	Newyork	356,185,131
21	Region 1	London	261,195,673
22	Region 1	Oslo	56,072,599
23	Region 1	Sydney	117,926,597
Sheet1

Cell Formulas
Range	Formula
A20:A23	A20	=FILTER(A2:A10, (A2:A10=F3) * (INDEX(C2:D10, 0, MATCH(G3, C2:D2, 0)) <> ""))
B20:B23	B20	=FILTER(B2:B10, (A2:A10=F3) * (INDEX(C2:D10, 0, MATCH(G3, C2:D2, 0)) <> ""))
C20:C23	C20	=FILTER(FILTER(B2:D10, A2:A10=F3), B2:D2=G3)
Dynamic array formulas.

 

[Eric W]

Another option:

Book1
	A	B	C	D	E	F	G
1	Type	CITY	23-Dec	24-Dec		Type	Date
2	Region 1	Newyork	356,185,131.00	585,268,748.00		Region 1	23-Dec
3	Region 1	London	261,195,673.00	31,808,836.00
4	Region 1	Oslo	56,072,599.00	49,343,517.00
5	Region 1	Sydney	117,926,597.00	87,492,786.00
6	Region 2	Berlin	25,880,269.00	26,256,754.00
7	Region 2	Madrid	11,236,121.00	11,114,443.00
8	Region 2	Mumbai	7,061,843.00	6,973,618.00
9	Region 2	Tokyo	14,568,312.00	18,688,591.00
10
11
12
13	Answer
14	Region 1	Newyork	356,185,131.00
15	Region 1	London	261,195,673.00
16	Region 1	Oslo	56,072,599.00
17	Region 1	Sydney	117,926,597.00
18
Sheet3

Cell Formulas
Range	Formula
A14:C17	A14	=CHOOSECOLS(FILTER(A2:D9,A2:A9=F2),1,2,MATCH(G2,A1:D1,0))
Dynamic array formulas.