If cell contains one of several employees, returns the corresponding manager's name

[drewber]

New to this forum. I am wanting to automate some data entry on an error report I complete daily.

I would be entering in one of several employee's names in F2 and I want that person's manager's name to appear in G2. So this would then be for each row: F3 different employee, G2 that employee's manager.
Therefore, anytime I enter "Jenny" in a row in column F, "Xavier" appears in next cell in column G, "Ed" would return "Yancy", and so on.

Due to the nature of this report, there may be additions, deletions, changes to the employees/managers, so I would need to be able to add, subtract or change. George may quit. We might add Tammy as new employee under Xavier as manager. Ed may move to Zack's team, etc.

Thanks in advance!

 

[maabadi]

Welcome to MrExcel Message Board.
Where is your Index table for manger and Employee name. Please upload it by method I described at My Signature.( below of my post).

 

[drewber]

New to this forum. I am wanting to automate some data entry on an error report I complete daily.

I would be entering in one of several employee's names in F2 and I want that person's manager's name to appear in G2. So this would then be for each row: F3 different employee, G2 that employee's manager.
Therefore, anytime I enter "Jenny" in a row in column F, "Xavier" appears in next cell in column G, "Ed" would return "Yancy", and so on.View attachment 47563

Due to the nature of this report, there may be additions, deletions, changes to the employees/managers, so I would need to be able to add, subtract or change. George may quit. We might add Tammy as new employee under Xavier as manager. Ed may move to Zack's team, etc.

Thanks in advance!

MINISHEET;

 

[drewber]

Welcome to MrExcel Message Board.
Where is your Index table for manger and Employee name. Please upload it by method I described at My Signature.( below of my post).

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Date Reported	Reporter	Issue Reported	Reference	Code #	Employee	Manager		Employee	Manager
2	9/1/21	Anne	Not Transferred	jac	3	Andy	Xavier		Andy	Xavier
3	9/1/21	Mike	Not Transferred	acr	4	George	Zack		Betty	Zack
4	9/1/21	Anne	Wrong transfer	gge	5	Betty	Zack		Charlie	Quincy
5	9/1/21	Ingrid	Not Transferred	ese	7	Mark	Yancy		Dani	Zack
6	9/1/21	Anne	Acct Not managed	ewq	3	Paris	Yancy		Ed	Yancy
7	9/2/21	Ingrid	not set up correctly	ahr	2	Helena	Quincy		Frances	Quincy
8	9/2/21	Alexis	Not Transferred	hjt	6	Mark	Yancy		George	Zack
9	9/2/21	Alexis	Not Transferred	oyy	9	Andy	Xavier		Helena	Quincy
10	9/2/21	Alexis	Not Transferred	rde	4	Charlie	Quincy		Ishmael	Xavier
11	9/2/21	Alexis	Not Transferred	err	2	Paris	Yancy		Jenny	Xavier
12	9/2/21	Mike	not verified	err	1	Frances	Quincy		Kevin	Zack
13	9/2/21	Mike	Not verified	pjg	5	Ishmael	Xavier		Lana	Xavier
14	9/2/21	Lisa	Acct not managed	ott	3	Lana	Xavier		Mark	Yancy
15	9/3/21	Anne	No Case	yse	8	Ed	Yancy		Nancy	Xavier
16	9/3/21	Anne	Not Transferred	eew	0	Nancy	Xavier		Oscar	Zack
17	9/3/21	Brenda	Acct Not managed	aez	7	Kevin	Zack		Paris	Yancy
18	9/3/21	Ingrid	not verified	kes	4	Dani	Zack
19	9/3/21	Anne	No CCRa	qhe	3	Jenny	Xavier
20	9/7/21	Sue	Not requested	hjg	2	Oscar	Zack
Main

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Date Reported	Reporter	Issue Reported	Reference	Code #	Employee	Manager		Employee	Manager
2	01/09/2021	Anne	Not Transferred	jac	3	Andy	Xavier		Andy	Xavier
3	01/09/2021	Mike	Not Transferred	acr	4	George	Zack		Betty	Zack
4	01/09/2021	Anne	Wrong transfer	gge	5	Betty	Zack		Charlie	Quincy
5	01/09/2021	Ingrid	Not Transferred	ese	7	Mark	Yancy		Dani	Zack
6	01/09/2021	Anne	Acct Not managed	ewq	3	Paris	Yancy		Ed	Yancy
7	02/09/2021	Ingrid	not set up correctly	ahr	2	Helena	Quincy		Frances	Quincy
8	02/09/2021	Alexis	Not Transferred	hjt	6	Mark	Yancy		George	Zack
9	02/09/2021	Alexis	Not Transferred	oyy	9	Andy	Xavier		Helena	Quincy
10	02/09/2021	Alexis	Not Transferred	rde	4	Charlie	Quincy		Ishmael	Xavier
11	02/09/2021	Alexis	Not Transferred	err	2	Paris	Yancy		Jenny	Xavier
12	02/09/2021	Mike	not verified	err	1	Frances	Quincy		Kevin	Zack
13	02/09/2021	Mike	Not verified	pjg	5	Ishmael	Xavier		Lana	Xavier
14	02/09/2021	Lisa	Acct not managed	ott	3	Lana	Xavier		Mark	Yancy
15	03/09/2021	Anne	No Case	yse	8	Ed	Yancy		Nancy	Xavier
16	03/09/2021	Anne	Not Transferred	eew	0	Nancy	Xavier		Oscar	Zack
17	03/09/2021	Brenda	Acct Not managed	aez	7	Kevin	Zack		Paris	Yancy
18	03/09/2021	Ingrid	not verified	kes	4	Dani	Zack
19	03/09/2021	Anne	No CCRa	qhe	3	Jenny	Xavier
20	07/09/2021	Sue	Not requested	hjg	2	Oscar	Zack
21
22
Master

Cell Formulas
Range	Formula
J2:J17	J2	=INDEX($G$2:$G$200,MATCH(FILTER(I2:I200,I2:I200<>""),$F$2:$F$200,0))
Dynamic array formulas.

 

[Joe4]

You should be able to do this pretty easily using the VLOOKUIP function.
See here for an explanation and examples: How to use the Excel VLOOKUP function | Exceljet

So for your example, the formula in G2 would look like:

=VLOOKUP(F2,$I$2:$J$17,2,0)

and you can copy down for the rest of the rows.

 

[drewber]

You should be able to do this pretty easily using the VLOOKUIP function.
See here for an explanation and examples: How to use the Excel VLOOKUP function | Exceljet

So for your example, the formula in G2 would look like:

=VLOOKUP(F2,$I$2:$J$17,2,0)

and you can copy down for the rest of the rows.

Thanks so much! Had to do some tweaks but got it to work. you rock!

 

[Joe4]

You are welcome.
Glad we were able to help!