Forum Discussion
8932LDG
Jun 11, 2023Copper Contributor
How to combine if statement with VLOOKUP formula
I have a table (please see the sample below), and I need to fill two columns ('Manager's name' and 'Manager's email') based on the column 'report to'. The value in 'Report to' indicates the 'employee ID' of the manager. So for instance, employee 101 reports to employee 100. To that though, I need to add a condition: if employee A reports to employee B, and employee B has a value in the 'Job Title rank' equal to 0, then the manager's name and email for employee A must be filled with the manager's name and email employee B reports to.
If an employee reports to someone who has a rank higher than 0, like employee 109 who reports to employee 110. Employee 110 has a 'Job title rank' equal to 1, so manager's name and email columns for employee 109 should be filled with name and email from employee 110 row.
If instead the employee report to someone with rank 0, than it should take the values for the person above him. So as in the table below, Employee 104 reports to employee 109. Employee 109 has a 'Job Title rank' equal to 0. In this case being the rank equal to 0, manager's email and name for employee 104 should be filled with the manager's name and email employee 109 reports to (so looking at the table employee 104 should have employee 110 name and email in the manager's email and name column, because employee 109 how has a rank 0 reports to employee 110).
Employee ID | Employee email | Employee Name | Job Title Rank | Department | Report to | Manager’s Name | Manager’s Email |
100 | email address removed for privacy reasons | name0, surname0 | 4 | CEO | |||
101 | email address removed for privacy reasons | name1, surname1 | 3 | Exec director | 100 | ||
102 | email address removed for privacy reasons | name2, surname2 | 1 | Finance | 103 | ||
103 | email address removed for privacy reasons | name3, surname3 | 2 | Business Operations | 101 | ||
104 | email address removed for privacy reasons | name4, surname4 | 0 | BPI&IT | 109 | ||
105 | email address removed for privacy reasons | name5, surname5 | 0 | Finance | 102 | ||
106 | email address removed for privacy reasons | name6, surname6 | 0 | HR | 108 | ||
107 | email address removed for privacy reasons | name7, surname7 | 0 | Finance | 105 | ||
108 | email address removed for privacy reasons | name8, surname8 | 0 | HR | 111 | ||
109 | email address removed for privacy reasons | name9, surname9 | 0 | BPI&IT | 110 | ||
110 | email address removed for privacy reasons | name10, surname10 | 1 | BPI&IT | 103 | ||
111 | email address removed for privacy reasons | name11, surname11 | 1 | HR | 103 | ||
112 | email address removed for privacy reasons | name12, surname12 | 1 | Finance | 103 |
In G2:
=IF(F2="","",VLOOKUP(IF(VLOOKUP(F2,$A$2:$F$14,4,FALSE)=0,VLOOKUP(F2,$A$2:$F$14,6,FALSE),F2),$A$2:$F$14,3,FALSE))
In H2:
=IF(F2="","",VLOOKUP(IF(VLOOKUP(F2,$A$2:$F$14,4,FALSE)=0,VLOOKUP(F2,$A$2:$F$14,6,FALSE),F2),$A$2:$F$14,2,FALSE))
Fill down.
In G2:
=IF(F2="","",VLOOKUP(IF(VLOOKUP(F2,$A$2:$F$14,4,FALSE)=0,VLOOKUP(F2,$A$2:$F$14,6,FALSE),F2),$A$2:$F$14,3,FALSE))
In H2:
=IF(F2="","",VLOOKUP(IF(VLOOKUP(F2,$A$2:$F$14,4,FALSE)=0,VLOOKUP(F2,$A$2:$F$14,6,FALSE),F2),$A$2:$F$14,2,FALSE))
Fill down.