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:

[TABLE="width: 495"]
<tbody>[TR]
[TD][/TD]
[TD]Associate Name:[/TD]
[TD]Joe Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Name[/TD]
[TD]EE ID[/TD]
[TD]Status[/TD]
[TD]Account Name[/TD]
[TD="colspan: 2"]Account number[/TD]
[/TR]
[TR]
[TD] Rec 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rec 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rec 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rec 4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rec 5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col><col><col span="5"></colgroup>[/TABLE]


Detail Sheet

[TABLE="width: 554"]
<tbody>[TR]
[TD]Name[/TD]
[TD]EE ID[/TD]
[TD]Status[/TD]
[TD]Account Name[/TD]
[TD]Account number[/TD]
[/TR]
[TR]
[TD]Joe Smith[/TD]
[TD]Q062230[/TD]
[TD]Other[/TD]
[TD]RIVERSIDE SENIOR LI[/TD]
[TD="align: right"]20802252[/TD]
[/TR]
[TR]
[TD]Joe Smith[/TD]
[TD]Q052230[/TD]
[TD]Other[/TD]
[TD]DAYS INN-ATHENS #06[/TD]
[TD="align: right"]40804593[/TD]
[/TR]
[TR]
[TD]Jessica English[/TD]
[TD]F652230[/TD]
[TD]Full Commission[/TD]
[TD]MEALS ON WHEELS[/TD]
[TD="align: right"]40798878[/TD]
[/TR]
[TR]
[TD]Todd Williams[/TD]
[TD]F652230[/TD]
[TD]Full Commission[/TD]
[TD]CSL-WESTMINSTER ALF[/TD]
[TD="align: right"]70942586[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col></colgroup>[/TABLE]

 

[Aladin Akyurek]

detail

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr][tr][td]

1​

[/td][td] Name[/td][td] EE ID[/td][td] Status[/td][td] Account Name[/td][td] Account number[/td][/tr]
[tr][td]

2​

[/td][td] Joe Smith[/td][td] Q062230[/td][td] Other[/td][td] RIVERSIDE SENIOR LI[/td][td]

20802252​

[/td][/tr]
[tr][td]

3​

[/td][td] Joe Smith[/td][td] Q052230[/td][td] Other[/td][td] DAYS INN-ATHENS #06[/td][td]

40804593​

[/td][/tr]
[tr][td]

4​

[/td][td] Jessica English[/td][td] F652230[/td][td] Full Commission[/td][td] MEALS ON WHEELS[/td][td]

40798878​

[/td][/tr]
[tr][td]

5​

[/td][td] Todd Williams[/td][td] F652230[/td][td] Full Commission[/td][td] CSL-WESTMINSTER ALF[/td][td]

70942586​

[/td][/tr]
[/table]

lookup

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr][tr][td]

1​

[/td][td] Associate Name:[/td][td] Joe Smith[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

2​

[/td][td] [/td][td] 2[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

3​

[/td][td] idx[/td][td] Name[/td][td] EE ID[/td][td] Status[/td][td] Account Name[/td][td] Account number[/td][/tr]
[tr][td]

4​

[/td][td]

1​

[/td][td]Joe Smith[/td][td]Q062230[/td][td]Other[/td][td]RIVERSIDE SENIOR LI[/td][td]

20802252​

[/td][/tr]
[tr][td]

5​

[/td][td]

2​

[/td][td]Joe Smith[/td][td]Q052230[/td][td]Other[/td][td]DAYS INN-ATHENS #06[/td][td]

40804593​

[/td][/tr]
[tr][td]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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)))