# Help looking up multiple values using one key. VLOOKUP? INDEX? MATCH?

#### servcoor

##### New Member
Hello. I appreciate your help on this:

Cell A1 contains the name of a supervisor. Cells in A2:A20 need to be populated with the names of the supervisor's employees. A sheet in the same workbook named All Teams contains a table listing all supervisors and their employees columnar format. The supervisor's names all appear in Column A and their employees' names are found in Column F. So if the supervisor has 15 employees, the supervisor's name will be listed in Column A in 15 consecutive rows. Due to hiring/terminations/team reorganizations, this table changes frequently so the supervisor's names do not always land in the same rows when the sheet is update. What formula in A2:A20 can be used to return each of the supervisor's employee's names whenever the table on the All Teams sheet is updated? VLOOKUP alone doesn't do it. Thank you.

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
in A2 and enter with ctrl+shift+enter, not just enter
adjust sheet2 range in all places to fit your list in sheet2
Code:
``=IFERROR(INDEX(Sheet2!\$F\$2:\$F\$20,SMALL(IF(Sheet2!\$A\$2:\$A\$20=B\$1,ROW(Sheet2!\$A\$2:\$A\$20)-ROW(\$A\$2)+1),ROWS(\$A\$2:B2))),"")``

Last edited:
Stridhan, sorry for the delay in responding to your suggested solution. It worked. It took me a few attempts to figure out how to get it to work properly and to be able to modify it for several different sections of my worksheet but I've got it performing the way it should. I can't say I understand how it works, but it works and does exactly what I needed it to.

Thank you.

You're welcome

Replies
13
Views
250
Replies
4
Views
83
Replies
9
Views
198
Replies
1
Views
189
Replies
22
Views
711

1,196,484
Messages
6,015,466
Members
441,898
Latest member
kofafa

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back