Forum Discussion

8932LDG's avatar
8932LDG
Copper Contributor
Jun 06, 2023

VLookup formula based on two columns with condition

Hi I have a list with the following fields:

EmailForenameSurnameJob Title DepartmentPortal UsernameManagement UnitFull Email Address

 

I need to create 2 vlooup columns for manager and manager's email .

We have 5 roles (ordered by the lowest to the highest) in the organization (we have more, but 5 is enough to describe the problem that needs to be resolved)

Executive, Senior Executive,  Department manager, Divisional Manager, CEO

The manager field would be filled based on the 'Job Title ' 'Management Unit' and the 'Department'.

So if an employee is a department manager, the vlookup fomula should look for the for the row that has the same management unit as the employee and 'divisional manager' in the'  job title' field. Then concatenate the last name and the first name in the manager's column cell.

If the employee is  'Divisional manager', it should look for the row that has 'CEO' in the 'job title' and then concatenate the last name and the first name in the manager's column cell.

If the employee has as a role 'Executive' or 'senior executive' then the formula should look for the column that has the same 'department' value and 'department manager' in the job title. Then concatenate the last name and the first name in the manager's column cell.

For each one of those 3 cases it should also fill the 'manager's email' field accordingly (taking the value from the last column 'full email address' ).

Do you know if it's possible (and if it is do you have any clues on how) to create such a formula?

Formulas and Functions

1 Reply

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jun 06, 2023

    8932LDG 

    You can try these formulas along with a reference table to determine the next higher role. Enter the formulas with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

    =IFERROR(INDEX($A$2:$A$7,MATCH(I2&G2,$D$2:$D$7&$G$2:$G$7,0)),"")

    This is the formula in cell K2.

    =IFERROR(CONCATENATE(INDEX($B$2:$B$7,MATCH(I2&G2,$D$2:$D$7&$G$2:$G$7,0))," ",INDEX($C$2:$C$7,MATCH(I2&G2,$D$2:$D$7&$G$2:$G$7,0))),"")

    This is the formula in cell J2.

     

     

    All formulas are filled down to row 7 in this example.

     

    =IFERROR(INDEX($E$17:$E$21,MATCH(D2,$E$17:$E$21,0)+1),"")

    This is the formula in cell I2 which identifies the next higher role from the reference table.

Resources