Solved

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

Posted on 2016-10-21
6
20 Views
Last Modified: 2016-11-12
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
0
Comment
Question by:jgreenlee01
  • 3
  • 3
6 Comments
 
LVL 25

Expert Comment

by:ProfessorJimJam
Comment Utility
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 Comment

by:jgreenlee01
Comment Utility
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 Comment

by:jgreenlee01
Comment Utility
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))),"")
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:jgreenlee01
Comment Utility
On the test sheet that is attached to the original question, the return appears to be 0 with the Ctrl Shift Enter.
0
 
LVL 25

Accepted Solution

by:
ProfessorJimJam earned 500 total points (awarded by participants)
Comment Utility
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
 
LVL 25

Expert Comment

by:ProfessorJimJam
Comment Utility
.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now