asked on

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

Professor J

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

jgreenlee01

ASKER

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?

jgreenlee01

ASKER

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

=IFERROR(INDEX('ADP Hours Worked by Dept Report'!B:B,SMALL(IF(B13='ADP Hours Worked by Dept Report'!A:A,ROW('ADP Hours Worked by Dept Report'!B:B)-ROW('ADP Hours Worked by Dept Report'!$B$2)+1),ROWS('ADP Hours Worked by Dept Report'!$A$2:A2))),"")

jgreenlee01

ASKER

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

ASKER CERTIFIED SOLUTION

Professor J

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Professor J