WPS Spreadsheet: Use IF, IFS, and Lookup Formulas for Manager Names
Question details
The user needs to return a manager name based on a department or team value in a spreadsheet.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A department value is entered in a cell such as H2, and the sheet should display the corresponding manager using IF, IFS, XLOOKUP, or VLOOKUP.
- Observed behavior
- The desired result is a matching manager name, with a fallback such as "No Match" when the department is not listed.
Identify the cell containing the department value and decide whether the mapping will stay small or change regularly. For a maintainable setup, prepare a two-column table with department names in the first column and manager names in the second.
Use a department-to-manager lookup table with XLOOKUP
Use this approach when department assignments may change or when you want to add departments without rewriting a long formula.
A lookup table keeps department and manager names separate from the formula, making the worksheet easier to update. XLOOKUP is available in newer spreadsheet versions and can return a custom result when no match is found.
In an empty area of the WPS Spreadsheet worksheet, enter department names in one column and their manager names in the adjacent column, such as Admin/Peter, Education/John, Medical/Mary, and Surgical/Harry.
Click the cell where the manager name should appear, such as I2, beside the department value in H2.
In the formula bar or selected cell, enter =XLOOKUP(H2,DepartmentRange,ManagerRange,"No Match"), replacing DepartmentRange and ManagerRange with the actual department and manager ranges.
Press Enter, then drag the fill handle from the result cell down the column to return a manager for each department value.
Return manager names with nested IF functions
Use a nested IF formula when there are only a few fixed department-to-manager assignments and no lookup table is needed.
Use IFS for a shorter multi-condition formula
Use IFS when your WPS Spreadsheet version supports it and you prefer a readable formula with one condition per department.
Match Departments to Manager Names in WPS Spreadsheet
WPS Spreadsheet lets you build department-to-manager mappings with nested IF, IFS, XLOOKUP, or VLOOKUP formulas. Create a maintainable lookup table, return a custom no-match message, and fill the formula through the worksheet.
- 1. Open a spreadsheet: Launch WPS Office and open an existing workbook or choose Spreadsheet from the home screen to create a new file.
- 2. Prepare the department data: Enter department values in one column and create a department-to-manager table in two adjacent columns.
- 3. Insert a matching formula: Click the output cell and use the formula bar to enter an XLOOKUP, VLOOKUP, nested IF, or IFS formula.
- 4. Fill the results: Press Enter and drag the fill handle down to apply the formula to the remaining rows.
Frequently Asked Questions
Which formula is best for matching a department to a manager?
XLOOKUP with a separate department-to-manager table is usually the easiest to maintain. Use nested IF for a short, fixed list or VLOOKUP when XLOOKUP is unavailable.
How do I show text when no department matches?
Add "No Match" as the final XLOOKUP argument, as in =XLOOKUP(H2,DepartmentRange,ManagerRange,"No Match"). For VLOOKUP, wrap the formula in IFERROR, such as =IFERROR(VLOOKUP(H2,A2:B5,2,FALSE),"No Match").
Can I use IFS in WPS Spreadsheet?
Use IFS if your WPS Spreadsheet version supports the function. Add TRUE,"No Match" as the final condition and result to handle departments that are not listed.
Why does my lookup return the wrong manager?
Check that the department text matches exactly, that the lookup and return ranges have the same number of rows, and that VLOOKUP uses FALSE for an exact match.
How can I apply the manager formula to all rows?
Enter the formula in the first result cell, press Enter, then drag its fill handle down the column. Use relative references for each department row and absolute references for a fixed lookup table when needed.
