lookup for multiple returns

[y3tter]

I have a workbook that has employee info in sheet1. Sheet2 lists the employees dependents with corresponding employees social security number in column A. Some employees have multiple dependents listed in multiple rows. Is there a formula that I can use in sheet1 to list each dependent in column, F, G, H, etc.?


Sheet1
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]111-11-1111[/TD]
[TD]Smith[/TD]
[TD]Jon[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]222-22-2222[/TD]
[TD]Wilson[/TD]
[TD]Dave[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]333-33-3333[/TD]
[TD]Char[/TD]
[TD]Jan[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]444-44-4444[/TD]
[TD]Page[/TD]
[TD]Chris[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Sheet2

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]111-11-1111[/TD]
[TD]Smith[/TD]
[TD]Kim[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]111-11-1111[/TD]
[TD]Smith[/TD]
[TD]Syd[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]111-11-1111[/TD]
[TD]Smith[/TD]
[TD]Sandy[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]222-22-2222[/TD]
[TD]Wilson[/TD]
[TD]Jan[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]333-33-3333[/TD]
[TD]Char[/TD]
[TD]Sarah[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]333-33-3333[/TD]
[TD]Char[/TD]
[TD]Dave[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]444-44-4444[/TD]
[TD]Page[/TD]
[TD]Mike[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]444-44-4444[/TD]
[TD]Page[/TD]
[TD]Larry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F	G	H
1
2	111-11-1111	Smith	Jon			Kim	Syd	Sandy
3	222-22-2222	Wilson	Dave			Jan
4	333-33-3333	Char	Jan			Sarah	Dave
5	444-44-4444	Page	Chris			Mike	Larry
Sheet1

Cell Formulas
Range	Formula
F2	=IFERROR(INDEX(sheet2!$C$2:$C$9,AGGREGATE(15,6,ROW(sheet2!$C$2:$C$9)-ROW(sheet2!$C$2)+1/(sheet2!$A$2:$A$9=$A2),COLUMNS($A:A))),"")

 

[y3tter]

Perfect, thank you!

 

[Fluff]

You're welcome & thanks for the feedback