SOLVED

# How to combine if statement with VLOOKUP formula

Copper 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).

best response confirmed by 8932LDG (Copper Contributor)
Solution

# Re: How to combine if statement with VLOOKUP formula

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

# Re: How to combine if statement with VLOOKUP formula

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.