How to Combine IF and VLOOKUP to Return a Manager Name and Email in WPS Office Spreadsheets
Question details
The user needs to combine IF and VLOOKUP in WPS Office Spreadsheets to return a manager name and email based on the Report To employee ID and the manager's job-title rank.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with an employee lookup table and must select a manager by the Report To employee ID and the manager's job-title rank.
- Observed behavior
- The lookup requires nested IF and VLOOKUP formulas to return the correct manager name and email from the employee table.
Verify that your employee table is in a range similar to A2:F14, with employee IDs in column A, manager names in column C, job-title rank in column D, email addresses in column E, and Report To IDs in column F. Adjust the column numbers in the formulas below if your workbook layout differs.
Use nested IF and VLOOKUP formulas in G2 and H2
Use this solution when you need to return a manager name and email based on the Report To employee ID and job-title rank.
This method nests VLOOKUP inside IF to decide whether to use the Report To ID directly or look up the next manager ID when the job-title rank is 0. The same pattern populates the manager name in G2 and the email in H2.
Click cell G2 in the WPS Spreadsheets worksheet that contains your employee table.
Type =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)) and press Enter.
Select G2 again, drag the fill handle down through G14 or your last data row to copy the formula for all employees.
Click cell H2, type =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,5,FALSE)) and press Enter.
Drag the fill handle from H2 down through the last data row to apply the email lookup to all rows.
Return Manager Details with WPS Office Spreadsheets
WPS Office Spreadsheets supports the same IF and VLOOKUP combinations as Excel, so you can return a manager name and email using the same formula without changing your file.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office Spreadsheets and open the employee workbook that contains the table and Report To IDs.
- 2. Enter the nested manager formula: Click G2 and enter the IF/VLOOKUP formula shown above to return the manager name.
- 3. Enter the email formula: Click H2 and enter the email version using the final column index 5.
- 4. Fill formulas down the columns: Drag the fill handle from G2 and H2 down through your data rows to populate the results.
- 5. Verify and save the workbook: Check the returned manager names and emails, then save the file in .xlsx format.
Frequently Asked Questions
Why does the formula return a blank cell?
The formula starts with IF(F2="","",...), so it returns a blank when the Report To ID in F2 is empty. Enter a valid Report To employee ID to get a result.
What should I change if my email column is not column E?
In the H2 formula, change the final VLOOKUP column index from 5 to the actual email column number in your lookup range.
How do I apply the formulas to all rows?
Select G2, drag the fill handle down through the rows, then do the same in H2. WPS Spreadsheets adjusts the row references automatically.
What does the job-title rank check do?
It looks up the Report To employee’s rank in column D. If the rank is 0, the formula picks the next manager value from column F for the outer VLOOKUP; otherwise it uses the Report To ID directly.
Why do I get a #N/A error?
Check that the Report To ID exists in the first column of A2:F14 and that the range and column indexes match your actual table.
