Returning Multiple Matches based off of one lookup value

[Lilfiger19]

Hi Guys!

Can anyone tell me how to do the following?

I have a workbook with two sheets. The first sheet (Detail) provides all of the data and the second sheet (Lookup) houses a data validation list. I want to be able to return all matches based off of an associate name, once chosen from the drop-down list.

If I choose Employee A (Detail!$D3), and that employee has several records on the detail tab, I want it to automatically return all of those records below the drop down box. I have tried using MATCH, INDEX & VLOOKUP to no avail.

Lookup Sheet:

	Associate Name:	Joe Smith
	Name	EE ID	Status	Account Name	Account number
Rec 1
Rec 2
Rec 3
Rec 4
Rec 5

<tbody>
</tbody><colgroup><col><col><col span="5"></colgroup>

Detail Sheet

Name	EE ID	Status	Account Name	Account number
Joe Smith	Q062230	Other	RIVERSIDE SENIOR LI	20802252
Joe Smith	Q052230	Other	DAYS INN-ATHENS #06	40804593
Jessica English	F652230	Full Commission	MEALS ON WHEELS	40798878
Todd Williams	F652230	Full Commission	CSL-WESTMINSTER ALF	70942586

<tbody>
</tbody><colgroup><col><col><col><col><col></colgroup>

 

[Aladin Akyurek]

detail

Row\Col	A​	B​	C​	D​	E​
1​	Name	EE ID	Status	Account Name	Account number
2​	Joe Smith	Q062230	Other	RIVERSIDE SENIOR LI	20802252​
3​	Joe Smith	Q052230	Other	DAYS INN-ATHENS #06	40804593​
4​	Jessica English	F652230	Full Commission	MEALS ON WHEELS	40798878​
5​	Todd Williams	F652230	Full Commission	CSL-WESTMINSTER ALF	70942586​

lookup

Row\Col	A​	B​	C​	D​	E​	F​
1​	Associate Name:	Joe Smith
2​		2
3​	idx	Name	EE ID	Status	Account Name	Account number
4​	1​	Joe Smith	Q062230	Other	RIVERSIDE SENIOR LI	20802252​
5​	2​	Joe Smith	Q052230	Other	DAYS INN-ATHENS #06	40804593​
6​
7​

In B2 just enter:

=COUNTIFS(detail!A2:A5,B1)

In A4 control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$4:A4)<=$B$2,SMALL(IF(detail!$A$2:$A$5=$B$1,ROW(detail!$A$2:$A$5)-ROW(detail!$A$2)+1),ROWS($A$4:A4)),"")

In B4 just enter, copy across to F4, and down:

=IF($A4="","",INDEX(detail!$A$2:$E$5,$A4,MATCH(B$3,detail!$A$1:$E$1,0)))