How to Look Up a Manager by Job Title, Department, and Management Unit in Excel
Question details
The user needs a two-criteria lookup formula in WPS Spreadsheets or Excel that returns a manager's name or email based on role, department, and management unit.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user manages an employee or reference table with roles, departments, and management units and must automatically identify the matching manager for a given next-higher role and management unit.
- Observed behavior
- The lookup should return the matching manager email or full name; if no match exists, the formula should return an empty string instead of an error. Older Excel versions may require Ctrl+Shift+Enter for these array formulas.
Create a reference table with columns for Email, Surname, Forename, Role, and Management Unit. Make sure the lookup values, especially the management unit, match exactly between the lookup cells and the reference table.
Return a Manager's Email with an INDEX/MATCH Two-Criteria Lookup
Use this formula when the next-higher role and management unit must both match a reference table to return an email address.
This formula joins the next-higher role and management unit into a single lookup key, then uses INDEX/MATCH to find the corresponding email. IFERROR hides the error when no match exists.
In WPS Spreadsheets or Excel, open the workbook and create a reference table with Email in column A, Surname in column B, Forename in column C, Role in column D, and Management Unit in column G. Enter the next role in I2 and the management unit in G2.
Select the cell where the email should appear, type =IFERROR(INDEX($A$2:$A$7,MATCH(I2&G2,$D$2:$D$7&$G$2:$G$7,0)),"") and press Enter. In older Excel versions, press Ctrl+Shift+Enter to confirm it as an array formula.
Return the Manager's Full Name from Separate Surname and Forename Columns
Use this variation when the manager's name is split across surname and forename columns and you need to combine them in one lookup result.
Find the Next-Higher Role from a Role Reference Table
Use this helper formula when you need to calculate the next-higher role before running the two-criteria lookup.
Create a Two-Criteria Manager Lookup in WPS Spreadsheets
Use WPS Spreadsheets to open your Excel workbook and enter the same INDEX and MATCH formulas. WPS supports Excel-compatible functions, so the lookup works without changing your existing file or workflow.
- 1. Open the workbook in WPS Spreadsheets: Download and open WPS Office, then open the employee or manager reference workbook in WPS Spreadsheets.
- 2. Create the reference table: Type or paste the email, surname, forename, role, and management unit columns into the worksheet.
- 3. Enter the lookup formula: Select the result cell, type =IFERROR(INDEX($A$2:$A$7,MATCH(I2&G2,$D$2:$D$7&$G$2:$G$7,0)),"") and press Enter.
- 4. Verify the returned manager: Check the returned email or name against the role and management unit to confirm the lookup is correct.
Frequently Asked Questions
Why does my lookup formula return blank even when a manager should match?
Check the exact text in I2 and G2 against the reference table. Extra spaces, case differences, or hidden characters can break the match. Use TRIM or CLEAN to normalize the values if needed.
Do I need to press Ctrl+Shift+Enter in WPS Spreadsheets?
Current WPS Spreadsheets normally accepts the formula with Enter. Older Excel versions may require Ctrl+Shift+Enter for array formulas, so use that if the result is not correct.
Can I return both the email and full name at the same time?
Yes. Place the email formula in one cell and the surname/forename concatenation formula in another cell, using the same I2 and G2 lookup values.
How do I make the manager lookup update automatically when the role changes?
Set I2 to the next-higher role formula =IFERROR(INDEX($E$17:$E$21,MATCH(D2,$E$17:$E$21,0)+1),"") so the manager email or name updates automatically when the role changes.
Does WPS Spreadsheets support INDEX/MATCH array formulas in .xlsx files?
Yes, WPS Spreadsheets supports these Excel-compatible lookup formulas and .xlsx files, so you can open, edit, and save the workbook without converting or losing formatting.
