Search and Return ALL found values. HELP!

[aepadilla2]

[h=1]I need your help to develop an Excel formula that will look up a value and return ALL found values in a separate worksheet.

I have the following database and I will like to separate all employees with the same job title in a different worksheet.

(I've tried VLOOKUP and ARRAY formulas with no luck). Anything helps thank you very much!

[/IMG][/h]

 

[Eric W]

Welcome to the forum!

Here's an option. Here's a small subset of your sample:
Excel 2010

	A	B	C	D	E	F
1	Emp ID	Legal Name	Manager	Title	Shift	Hire date
2		Rene	David	PLANT MANAGER II
3		MARTHA	ROBIN	HUMAN RESOURCE MANAGER II
4	101566	ARTURO	KIM	STORE ROOM ATTENDANT		11/14/2007
5	103246	JESUS	RUBEN	SORTER		5/5/2008
6	113379	ALFRED	KIM	STORE ROOM ATTENDANT		3/5/2012

<tbody>
</tbody>

Sheet1​

And on Sheet2, here's what it looks like:
Excel 2010

	A	B	C	D	E	F	G	H	I
1	STORE ROOM ATTENDANT		ID	NAME	MANAGER	TITLE	SHIFT	HIRE	ON-LEAVE
2		4	101566	ARTURO	KIM	STORE ROOM ATTENDANT	0	39400	0
3		6	113379	ALFRED	KIM	STORE ROOM ATTENDANT	0	40973	0

<tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula C2 =IFERROR(INDEX(Sheet1!A$1:A$100,$B2),"") <tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula B2 {=IFERROR(AGGREGATE(15,3,IF(Sheet1!$D$2:$D$100=$A$1,ROW($D$2:$D$100),NA()),ROW()-ROW($B$2)+1),"")} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

In A1 on Sheet2, put the title you're looking for. Put the array formula in B2, confirm with Control-Shift-Enter. Then copy it and paste it down the column. Then take the regular formula in C2, copy it, and paste it through the rest of your range C2:I100 (or whatever). Change the bottom row in the formulas too. You can hide column B if you like. This formula requires Excel 2010 or later. Let me know how it works.

 

[JLGWhiz]

If you can use VBA, This might work. copy to standar code module 1.

Sub likeEmpList()
Dim sh As Worksheet
Set sh = Sheets("Sheet1")
    With sh
        .Range("D3", .Cells(Rows.Count, 4).End(xlUp)).AdvancedFilter xlFilterCopy, , .Cells(Rows.Count, 1).End(xlUp)(3), True
        For Each c In .Range(.Cells(Rows.Count, 1).End(xlUp), .Cells(Rows.Count, 1).End(xlUp).End(xlUp)(2))
            .UsedRange.Offset(2).AutoFilter 4, c.Value
            Sheets.Add After:=Sheets(Sheets.Count)
            .UsedRange.Offset(3).SpecialCells(xlCellTypeVisible).EntireRow.Copy ActiveSheet.Range("A2")
            .Rows(1).Copy ActiveSheet.Range("A1")
            ActiveSheet.Name = c.Value
            .AutoFilterMode = False
        Next
    End With
End Sub

 

[Eric W]

JLGWhiz's code sparked a thought. You don't need formulas or VBA if you don't want. You can filter your table by job title, then copy the visible rows, then paste those to another sheet. Very quick.