FILTER with Criteria

[kumara_faith]

Hi,

I have the following :

Book1
	C	D	E
2	Table 1
3	Rank	Category	KPI
4	Manager	Sales1	500
5	Manager	Sales2	200
6	Manager	Sales3	300
7	Manager	Sales4	500
8	Manager	M1
9	Manager	M2
10	Asst Manager	Sales1	500
11	Asst Manager	Sales2	200
12	Asst Manager	T1
13	Asst Manager	T2
14	Asst Manager	Sales5	200
15	Asst Manager	Sales6	600
16	Sewnior Executive	Sales1	500
17	Sewnior Executive	Sales2	200
18	Sewnior Executive	Q1
19	Sewnior Executive	Q2
20	Sewnior Executive	Sales5	200
21	Sewnior Executive	Sales6	600
Sheet1

Book1
	I	J	K	L
2	Table 2
3	Rank	Branch	Category	KPI
4	Manager	Branch1	M1	10
5	Manager	Branch2	M1	200
6	Manager	Branch3	M1	500
7	Manager	Branch4	M1	300
8	Manager	Branch1	M2	100
9	Manager	Branch2	M2	800
10	Manager	Branch3	M2	400
11	Manager	Branch4	M2	200
12	Asst Manager	Branch1	T1	10
13	Asst Manager	Branch2	T1	200
14	Asst Manager	Branch3	T1	500
15	Asst Manager	Branch4	T1	300
16	Asst Manager	Branch1	T2	100
17	Asst Manager	Branch2	T2	800
18	Asst Manager	Branch3	T2	400
19	Asst Manager	Branch4	T2	200
20	Sewnior Executive	Branch1	Q1	100
21	Sewnior Executive	Branch2	Q1	800
22	Sewnior Executive	Branch3	Q1	400
23	Sewnior Executive	Branch4	Q1	350
24	Sewnior Executive	Branch1	Q2	10
25	Sewnior Executive	Branch2	Q2	500
26	Sewnior Executive	Branch3	Q2	200
27	Sewnior Executive	Branch4	Q2	300
Sheet1

Book1
	O	P	Q
2	Table 3
3	Rank	Manager
4	Branch	Branch1
5	Manager	Sales1	500
6	Manager	Sales2	200
7	Manager	Sales3	300
8	Manager	Sales4	500
9	Manager	M1	0
10	Manager	M2	0
Sheet1 (2)

Cell Formulas
Range	Formula
O5:Q10	O5	=FILTER(C4:E21,C4:C21=P3)
Dynamic array formulas.

In Table 1, I have the KPI for all ranks of staffs. In Table 2, I have the KPI for all staff ranks for those KPI which are blank in Table 1. In Table 3, I am trying to auto populate the KPI based on the rank and branch and KPI in Table 1 and the remaining KPI in Table 2.. I managed to populate the first set of information from Table 1 but I am not sure how to populate the information from Table 2. An example of the correct result for Manager will be as follows:

Book1
	O	P	Q
2	Table 3
3	Rank	Manager
4	Branch	Branch1
5	Manager	Sales1	500
6	Manager	Sales2	200
7	Manager	Sales3	300
8	Manager	Sales4	500
9	Manager	M1	10
10	Manager	M2	100
Sheet1

Appreciate any help how to accomplish this.

 

[JvdV]

SOmeone can probably come up with something shorter, but this works:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Table 1				Table 2					Table 3
2	Rank	Category	KPI		Rank	Branch	Category	KPI		Rank	Manager
3	Manager	Sales1	500		Manager	Branch1	M1	10		Branch	Branch1
4	Manager	Sales2	200		Manager	Branch2	M1	200		Manager	Sales1	500
5	Manager	Sales3	300		Manager	Branch3	M1	500		Manager	Sales2	200
6	Manager	Sales4	500		Manager	Branch4	M1	300		Manager	Sales3	300
7	Manager	M1			Manager	Branch1	M2	100		Manager	Sales4	500
8	Manager	M2			Manager	Branch2	M2	800		Manager	M1	10
9	Asst Manager	Sales1	500		Manager	Branch3	M2	400		Manager	M2	100
10	Asst Manager	Sales2	200		Manager	Branch4	M2	200
11	Asst Manager	T1			Asst Manager	Branch1	T1	10
12	Asst Manager	T2			Asst Manager	Branch2	T1	200
13	Asst Manager	Sales5	200		Asst Manager	Branch3	T1	500
14	Asst Manager	Sales6	600		Asst Manager	Branch4	T1	300
15	Sewnior Executive	Sales1	500		Asst Manager	Branch1	T2	100
16	Sewnior Executive	Sales2	200		Asst Manager	Branch2	T2	800
17	Sewnior Executive	Q1			Asst Manager	Branch3	T2	400
18	Sewnior Executive	Q2			Asst Manager	Branch4	T2	200
19	Sewnior Executive	Sales5	200		Sewnior Executive	Branch1	Q1	100
20	Sewnior Executive	Sales6	600		Sewnior Executive	Branch2	Q1	800
21					Sewnior Executive	Branch3	Q1	400
22					Sewnior Executive	Branch4	Q1	350
23					Sewnior Executive	Branch1	Q2	10
24					Sewnior Executive	Branch2	Q2	500
25					Sewnior Executive	Branch3	Q2	200
26					Sewnior Executive	Branch4	Q2	300
Sheet6

Cell Formulas
Range	Formula
J4:L9	J4	=FILTER(HSTACK(A3:B20,BYROW(A3:C20,LAMBDA(x,IF(DROP(x,,2)<>"",DROP(x,,2),SUMIFS(H3:H26,F3:F26,K3,E3:E26,TAKE(x,,1),G3:G26,INDEX(x,,2)))))),A3:A20=K2)
Dynamic array formulas.

 

[Fluff]

Another option

Fluff.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2	Table 1						Table 2						Table 3
3	Rank	Category	KPI				Rank	Branch	Category	KPI			Rank	Manager
4	Manager	Sales1	500				Manager	Branch1	M1	10			Branch	Branch1
5	Manager	Sales2	200				Manager	Branch2	M1	200			Manager	Sales1	500
6	Manager	Sales3	300				Manager	Branch3	M1	500			Manager	Sales2	200
7	Manager	Sales4	500				Manager	Branch4	M1	300			Manager	Sales3	300
8	Manager	M1					Manager	Branch1	M2	100			Manager	Sales4	500
9	Manager	M2					Manager	Branch2	M2	800			Manager	M1	10
10	Asst Manager	Sales1	500				Manager	Branch3	M2	400			Manager	M2	100
11	Asst Manager	Sales2	200				Manager	Branch4	M2	200
12	Asst Manager	T1					Asst Manager	Branch1	T1	10
13	Asst Manager	T2					Asst Manager	Branch2	T1	200
14	Asst Manager	Sales5	200				Asst Manager	Branch3	T1	500
15	Asst Manager	Sales6	600				Asst Manager	Branch4	T1	300
16	Sewnior Executive	Sales1	500				Asst Manager	Branch1	T2	100
17	Sewnior Executive	Sales2	200				Asst Manager	Branch2	T2	800
18	Sewnior Executive	Q1					Asst Manager	Branch3	T2	400
19	Sewnior Executive	Q2					Asst Manager	Branch4	T2	200
20	Sewnior Executive	Sales5	200				Sewnior Executive	Branch1	Q1	100
21	Sewnior Executive	Sales6	600				Sewnior Executive	Branch2	Q1	800
22							Sewnior Executive	Branch3	Q1	400
23							Sewnior Executive	Branch4	Q1	350
24							Sewnior Executive	Branch1	Q2	10
25							Sewnior Executive	Branch2	Q2	500
26							Sewnior Executive	Branch3	Q2	200
27							Sewnior Executive	Branch4	Q2	300
Main

Cell Formulas
Range	Formula
O5:Q10	O5	=LET(f,FILTER(C4:E21,C4:C21=P3),IF(f="",SUMIFS(L:L,I:I,P3,J:J,P4,K:K,INDEX(f,,2)),f))
Dynamic array formulas.

 

[kumara_faith]

Hi Fluff and JvDv,

Thank you for the solution and appreciate it. Have a great day ahead.

 

[Fluff]

Glad we could help & thanks for the feedback.