sort and filter function with multiple criteria on "include"

[ExcelNewbie2020]

how to filter sheet1 with the "include" criteria from multiple values (sheet 2: name).
basically if names from sheet 1 is available in sheet 2, then a formula in sheet 3 will extract the names/dept/group from sheet 1.

Book2
	A	B	C	D	E	F	G	H	I	J	K
3									EXPECTED RESULT
4	sheet 1				sheet 2				SHEET 3
5	name	dept.	group		name				name1	A	1
6	name1	A	1		name1				name2	B	2
7	name2	B	2		name10				name3	C	2
8	name3	C	2		name7				name7	B	2
9	name4	D	1		name3				name8	C	1
10	name5	E	1		name2				name10	E	1
11	name6	A	2		name3
12	name7	B	2		name1
13	name8	C	1		name7
14	name9	D	2		name2
15	name10	E	1		name8
Sheet1

 

[Peter_SSs]

I have done it on a single sheet like you have shown, but if you point the formula at the correct ranges there should be no trouble adapting to 3 sheets.
your thread title mentions "sort" but your description and sample didn't clarify that so i have done no sorting.

23 03 11.xlsm
	A	B	C	D	E	F	G	H	I	J	K
3									EXPECTED RESULT
4	sheet 1				sheet 2				SHEET 3
5	name	dept.	group		name				name1	A	1
6	name1	A	1		name1				name2	B	2
7	name2	B	2		name10				name3	C	2
8	name3	C	2		name7				name7	B	2
9	name4	D	1		name3				name8	C	1
10	name5	E	1		name2				name10	E	1
11	name6	A	2		name3
12	name7	B	2		name1
13	name8	C	1		name7
14	name9	D	2		name2
15	name10	E	1		name8
16
Filter

Cell Formulas
Range	Formula
I5:K10	I5	=FILTER(A6:C15,ISNUMBER(MATCH(A6:A15,E6:E15,0)))
Dynamic array formulas.

 

[ExcelNewbie2020]

I have done it on a single sheet like you have shown, but if you point the formula at the correct ranges there should be no trouble adapting to 3 sheets.
your thread title mentions "sort" but your description and sample didn't clarify that so i have done no sorting.

23 03 11.xlsm
	A	B	C	D	E	F	G	H	I	J	K
3									EXPECTED RESULT
4	sheet 1				sheet 2				SHEET 3
5	name	dept.	group		name				name1	A	1
6	name1	A	1		name1				name2	B	2
7	name2	B	2		name10				name3	C	2
8	name3	C	2		name7				name7	B	2
9	name4	D	1		name3				name8	C	1
10	name5	E	1		name2				name10	E	1
11	name6	A	2		name3
12	name7	B	2		name1
13	name8	C	1		name7
14	name9	D	2		name2
15	name10	E	1		name8
16
Filter

Cell Formulas
Range	Formula
I5:K10	I5	=FILTER(A6:C15,ISNUMBER(MATCH(A6:A15,E6:E15,0)))
Dynamic array formulas.

i thought the basis will be sheet 2 (name) it will need sorting. i guess i'm wrong.. actually, I tried to use that filter and match it doesn't work. It need to insert ISNUMBER to make it work. thanks sir for the new idea..

 

[Peter_SSs]

So are you all sorted now?

 

[ExcelNewbie2020]

So are you all sorted now?

absolutely..

 

[Peter_SSs]

OK, good news!