Trying to create a formula or macro to pull multiple cells to a new sheet

[jimbrown21]

I have a macro built which pulls employee information onto sheet 1 from a website so it can be current.
Column B= Employee ID
Column D= Employee Name
Column G= Manager Name
I would like to build a formula or macro onto sheet 2 which pulls info from sheet 1. I would like to put into column A&B on sheet2 the Employee ID and Employee name IF their manager has one of the two names which I typed into cells V1 and V2 on sheet2.

Anyone know how I would go about doing this function?

 

[maabadi]

Welcome to MrExcel Message Board.

Example Data at Sheet1

Book1.xlsx
	A	B	C	D	E	F	G
1		Employee ID		Employee Name		Manager Name
2		11001		Peter1		Linch
3		11002		Peter2		Frank
4		11003		Peter3		Jeny
5		11004		Peter4		Heidi
6		11005		Harold1		Alexander
7		11006		Harold2		Linch
8		11007		Harold3		Frank
9		11008		John1		Jeny
10		11009		John2		Heidi
11		11010		John3		Alexander
12		11011		John4		Linch
13		11012		John5		Frank
14		11013		Kathrin1		Jeny
15		11014		Kathrin2		Heidi
16		11015		Sara1		Alexander
17		11016		Sara2		Linch
18		11017		Sara3		Frank
19		11018		Sara4		Jeny
20		11019		Jessica1		Heidi
21		11020		Jessica2		Alexander
22		11021		Jessica3		Linch
23		11022		Morris1		Frank
24		11023		Morris2		Jeny
25
Sheet1

AND Result at Sheet2 (Column A formula should Press CTRL+SHIFT+ENTER

Book1.xlsx
	A	B	C																		U	V
1	EmployeeID	Employee Name	Manager Name																			Linch
2	11001	Peter1	Linch																			Jeny
3	11003	Peter3	Jeny
4	11006	Harold2	Linch
5	11008	John1	Jeny
6	11011	John4	Linch
7	11013	Kathrin1	Jeny
8	11016	Sara2	Linch
9	11018	Sara4	Jeny
10	11021	Jessica3	Linch
11	11023	Morris2	Jeny
12
13
14
15
Sheet2

Cell Formulas
Range	Formula
A2:A13	A2	=IFERROR(INDEX(Sheet1!$B$2:$B$24,SMALL(IF((Sheet1!$F$2:$F$24=$V$1)+(Sheet1!$F$2:$F$24=$V$2),ROW(Sheet1!$B$2:$B$24)-ROW(Sheet1!$B$2)+1),ROWS($A$2:A2))),"")
B2:C13	B2	=IFNA(VLOOKUP($A2,Sheet1!$B$2:$F$24,COLUMNS($A$1:B1)*2-1,FALSE),"")
Press CTRL+SHIFT+ENTER to enter array formulas.