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

111-11-1111	Smith	Jon
222-22-2222	Wilson	Dave
333-33-3333	Char	Jan
444-44-4444	Page	Chris

<tbody>
</tbody>

Sheet2

111-11-1111	Smith	Kim
111-11-1111	Smith	Syd
111-11-1111	Smith	Sandy
222-22-2222	Wilson	Jan
333-33-3333	Char	Sarah
333-33-3333	Char	Dave
444-44-4444	Page	Mike
444-44-4444	Page	Larry

<tbody>
</tbody>

 

[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