Suppose I have the following dataframe (Manager ID column is blank and we may have missing or wrong values in any column like in the example):
Employee ID Email Manager Email Manager ID
1 x@aa.com aaa@aa.com
2 z@aa.com aaa@aa.com
3 t@aa.com aaa@aa.com
1 x@aa.com bbb.@aa.com
4 aaa@aa.com zzz@aa.com
5 bbb@aa.com
6 zzz@aa.com ^
7 # x@aa.com
I need a way to get all "Manager Email" values and use it to look into the "Email" column in order to get the correspondent "Employee ID" in case the value exist in the "Email" column. If not, leave the cell blank. The output expected would be:
Employee ID Email Manager Email Manager ID
1 x@aa.com aaa@aa.com 4
2 z@aa.com aaa@aa.com 4
3 t@aa.com aaa@aa.com 4
1 x@aa.com bbb.@aa.com
4 aaa@aa.com zzz@aa.com 6
5 bbb@aa.com
6 zzz@aa.com ^
7 # x@aa.com 1
What would be the best way to perform this lookup?
Thank you!