How/which formula can be used to lookup a value in a table and return multiple matches in table format In Excel?

[ajesh27]

I have a dataset like below

when I select Department from the Dropdown

I need to return multiple matches in atable format like below

Which Formula can i use to get these multiple matches in a table format....Thanks in advance

 

[maabadi]

Try this:

Book1
	A	B	C	D	E	F	G	H	I
1	Name	Department	Grade
2	Candidate1	EC	A		Department	CE
3	Candidate2	ME	B
4	Candidate3	EE	B		Name	Department	Grade
5	Candidate4	CE	A		Candidate4	CE	A
6	Candidate5	EC	C		Candidate8	CE	C
7	Candidate6	ME	D		Candidate12	CE	D
8	Candidate7	EE	B
9	Candidate8	CE	C
10	Candidate9	EC	D
11	Candidate10	ME	A
12	Candidate11	EE	C
13	Candidate12	CE	D
14	Candidate13	EC	A
15
16
Sheet1

Cell Formulas
Range	Formula
E5:G13	E5	=IFERROR(INDEX($A$2:$C$14,SMALL(IF($F$2=$B$2:$B$14,ROW($B$2:$B$14)-ROW($B$2)+1),ROWS($E$1:$E1)),COLUMNS($E$1:E$1)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[ajesh27]

Thanks it worked

 

[maabadi]

You're Welcome & Thanks for Feedback.