Return multiple matches for lookup

[KGee]

I have two separate Excel files that have a common ID between them. File one is considered the master and each ID appears one time only. The second file includes the same ID's and also includes a list of staff names. In the cell next to the ID in file one, I want to list all of the names from file two that have a match for the ID. Below is an example of what I am trying to achieve. Is it possible to use a vlookup to return multiple values or can a script be written that would loop through the files to compile the names? Also, the names don't have to appended in the same cell, they can be listed in adjacent cells as long as they are in the same row as the ID.

[TABLE="width: 500"]
<tbody>[TR]
[TD]FILE 1 (ID)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC1000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC1001[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ABC1002[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FILE 2 (NAME)[/TD]
[TD](ID)[/TD]
[/TR]
[TR]
[TD]Name 1[/TD]
[TD]ABC1000[/TD]
[/TR]
[TR]
[TD]Name 1[/TD]
[TD]ABC1001[/TD]
[/TR]
[TR]
[TD]Name 2[/TD]
[TD]ABC1000[/TD]
[/TR]
[TR]
[TD]Name 3[/TD]
[TD]ABC1000[/TD]
[/TR]
[TR]
[TD]Name 4[/TD]
[TD]ABC1001[/TD]
[/TR]
[TR]
[TD]Name 4[/TD]
[TD]ABC1002[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DESIRED RESULT[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FILE 1 (ID)[/TD]
[TD](NAME)[/TD]
[/TR]
[TR]
[TD]ABC1000[/TD]
[TD]Name 1, Name 2, Name 3[/TD]
[/TR]
[TR]
[TD]ABC1001[/TD]
[TD]Name 1, Name 4[/TD]
[/TR]
[TR]
[TD]ABC1002[/TD]
[TD]Name 4[/TD]
[/TR]
</tbody>[/TABLE]

 

[AhoyNC]

Try:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula across and down.

Excel Workbook
	A	B	C	D
1	NAME	(ID)
2	Name 1	ABC1000
3	Name 1	ABC1001
4	Name 2	ABC1000
5	Name 3	ABC1000
6	Name 4	ABC1001
7	Name 4	ABC1002
8
9
10
11	ID	Name
12	ABC1000	Name 1	Name 2	Name 3
13	ABC1001	Name 1	Name 4
14	ABC1002	Name 4
Sheet

 

[Fluff]

Untested, but try

Sub GetNames()
   Dim Cl As Range
   Dim Dic As Object
   Dim Ws1 As Worksheet
   Dim Ws2 As Worksheet
   
   Set Ws1 = [COLOR=#ff0000]Workbooks("book1.xlsm").Sheets(1)[/COLOR]
   Set Ws2 = [COLOR=#ff0000]Workbooks("book2.xlsm").Sheets(1)[/COLOR]
   Set Dic = CreateObject("scripting.dictionary")
   For Each Cl In Ws2.Range("B2", Ws2.Range("B" & Rows.Count).End(xlUp))
      If Not Dic.exists(Cl.Value) Then
         Dic.Add Cl.Value, Cl.Offset(, -1).Value
      Else
         Dic(Cl.Value) = Dic(Cl.Value) & ", " & Cl.Offset(, -1).Value
      End If
   Next Cl
   For Each Cl In Ws1.Range("A2", Ws1.Range("A" & Rows.Count).End(xlUp))
      If Dic.exists(Cl.Value) Then Cl.Offset(, 1).Value = Dic(Cl.Value)
   Next Cl

End Sub

Change workbook/worksheet names to suit.
Both workbooks need to be open when you run this.

 

[KGee]

I tried the first option and that worked. Thanks Ahoy