SOLVED

How to combine if statement with VLOOKUP formula

Copper Contributor

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 IDEmployee emailEmployee NameJob Title RankDepartmentReport toManager’s NameManager’s Email
100email address removed for privacy reasonsname0, surname04CEO   
101email address removed for privacy reasonsname1, surname13Exec director100  
102email address removed for privacy reasonsname2, surname21Finance103  
103email address removed for privacy reasonsname3, surname32Business Operations101  
104email address removed for privacy reasonsname4, surname40BPI&IT109  
105email address removed for privacy reasonsname5, surname50Finance102  
106email address removed for privacy reasonsname6, surname60HR108  
107email address removed for privacy reasonsname7, surname70Finance105  
108email address removed for privacy reasonsname8, surname80HR111  
109email address removed for privacy reasonsname9, surname90BPI&IT110  
110email address removed for privacy reasonsname10, surname101BPI&IT103  
111email address removed for privacy reasonsname11, surname111HR103  
112email address removed for privacy reasonsname12, surname121Finance103  
1 Reply
best response confirmed by 8932LDG (Copper Contributor)
Solution

@8932LDG 

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.

1 best response

Accepted Solutions
best response confirmed by 8932LDG (Copper Contributor)
Solution

@8932LDG 

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.

View solution in original post