lookup for multiple returns

y3tter

Board Regular
Joined
Nov 11, 2012
Messages
142
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-1111SmithJon
222-22-2222WilsonDave
333-33-3333CharJan
444-44-4444PageChris

<tbody>
</tbody>


Sheet2

111-11-1111SmithKim
111-11-1111SmithSyd
111-11-1111SmithSandy
222-22-2222WilsonJan
333-33-3333CharSarah
333-33-3333CharDave
444-44-4444PageMike
444-44-4444PageLarry

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,264
Office Version
  1. 365
Platform
  1. Windows
How about

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">111-11-1111</td><td style=";">Smith</td><td style=";">Jon</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Kim</td><td style=";">Syd</td><td style=";">Sandy</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">222-22-2222</td><td style=";">Wilson</td><td style=";">Dave</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Jan</td><td style=";"></td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">333-33-3333</td><td style=";">Char</td><td style=";">Jan</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Sarah</td><td style=";">Dave</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">444-44-4444</td><td style=";">Page</td><td style=";">Chris</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Mike</td><td style=";">Larry</td><td style=";"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F2</th><td style="text-align:left">=IFERROR(<font color="Blue">INDEX(<font color="Red">sheet2!$C$2:$C$9,AGGREGATE(<font color="Green">15,6,ROW(<font color="Purple">sheet2!$C$2:$C$9</font>)-ROW(<font color="Purple">sheet2!$C$2</font>)+1/(<font color="Purple">sheet2!$A$2:$A$9=$A2</font>),COLUMNS(<font color="Purple">$A:A</font>)</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,264
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,108,910
Messages
5,525,585
Members
409,651
Latest member
Quasar Hunter

This Week's Hot Topics

Top