# Return first occurence of match, return the next match on next occurence

I have attached a spreadsheet here to demonstrate what I'm trying to do.

I have a list, with a department number and an employee name on one spreadsheet.  More than one employee may work in a department.

I need to have each person who matches a department show up , I've tried a Vlookup formula however this only returns the first person,  I also tried a match formula,  any assistance would be greatly appreciated!
Test-Payroll-Automation.xlsx
###### Who is Participating?

x

Commented:
put this formula in C9 and copy down

=IFERROR(INDEX(MyData[Payroll Name],SMALL(IF('Main Street'!B9=MyData[Worked In Department],ROW(MyData[Payroll Name])-ROW('ADP Hours Worked by Dept Report'!\$B2)+1),COUNTIF('Main Street'!\$B\$9:\$B9,'Main Street'!\$B9))),"")
0

Commented:
use this formula, it is array formula so enter it with special key stroke of Control + Shift +Enter

see attached

=IFERROR(INDEX(Sheet2!\$B\$2:\$B\$9,SMALL(IF(Sheet1!B9=Sheet2!\$A\$2:\$A\$9,ROW(Sheet2!\$B\$2:\$B\$9)-ROW(Sheet2!\$B\$2)+1),ROWS(Sheet2!\$A\$2:A2))),"")
Test-Payroll-Automation.xlsx
0

Author Commented:
Hi Professor JimJam,

Thank you,

When I apply this solution to the larger spreadsheet I am not getting the right names produced by the formula.  A random name appears to show up.  What could I be misapplying?
0

Author Commented:
This is what my formula looks like when converted to work for the larger spreadsheet

0

Author Commented:
On the test sheet that is attached to the original question, the return appears to be 0 with the Ctrl Shift Enter.
0

Commented:
.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.