How to Look Up Roles and Required Training Across Excel Sheets in WPS Spreadsheets
Question details
The user needs to create a training log that retrieves each person's assigned role from one sheet and returns the corresponding training requirements from another sheet using lookup formulas.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Building a training log that maps people to roles and role-based training requirements across worksheets.
- Observed behavior
- The goal is for the formula to automatically return the correct training requirements after a person's role is identified.
Create or identify the 'Roles Assigned' sheet that maps each person to a role, and the 'Safeguarding Requirements' sheet that lists the training requirements for each role. Make sure names and roles use consistent spelling and no extra spaces.
Use a Nested VLOOKUP Formula for One Role per Person
Use this when each person has exactly one assigned role and the training requirement is stored in a separate lookup sheet.
This formula first looks up the person's role in the Roles Assigned sheet, then uses that role as the lookup value for the Safeguarding Requirements sheet.
Open your WPS Office workbook and click the cell where you want the required training to appear, for example C2 on the training log sheet.
Type =VLOOKUP(VLOOKUP(A2,'Roles Assigned'!A:B,2,FALSE),'Safeguarding Requirements'!A:B,2,FALSE) and adjust sheet names and ranges to match your workbook.
Press Enter to run the formula. The cell should show the training requirement associated with the role assigned in cell A2.
Use the fill handle to copy the formula down the column for other employees.
Use XLOOKUP for a More Flexible Lookup
Use this when your version supports XLOOKUP and you want a more readable formula with separate lookup and return ranges.
Use FILTER or Power Query When a Person Can Have Multiple Roles
Use this when a person can have more than one role and you need to return all applicable training requirements, not only the first match.
Create Role-Based Training Lookups in WPS Office Spreadsheets
Use WPS Office Spreadsheets to build your training log with VLOOKUP, XLOOKUP, or FILTER formulas that automatically pull each person's role and required training. WPS Spreadsheets supports the same workbook formulas and .xlsx compatibility as Microsoft Excel, so you can apply these lookups without changing your existing files.
- 1. Prepare your lookup sheets: Open your workbook in WPS Spreadsheets and make sure the Roles Assigned and Safeguarding Requirements sheets are available with consistent role names.
- 2. Enter the lookup formula: Click the result cell and enter the nested VLOOKUP, XLOOKUP, or FILTER formula shown in the solutions above.
- 3. Adjust ranges to your workbook: Update the sheet names and column references in the formula so they match your actual data layout.
- 4. Copy the formula down: Use the fill handle to apply the formula to all employees in the training log.
Frequently Asked Questions
Why does my nested VLOOKUP return #N/A?
Usually the person's name or role is not an exact match. Check for extra spaces, different spelling, or a missing role in the Roles Assigned or Safeguarding Requirements sheet, and make sure you use FALSE for an exact match.
Can XLOOKUP replace VLOOKUP in WPS Office Spreadsheets?
Yes. In supported WPS Spreadsheets versions you can use XLOOKUP with separate lookup and return ranges, for example =XLOOKUP(value,lookup_range,return_range). Adjust the ranges to match your sheets.
How do I return multiple training requirements when a person has multiple roles?
Use FILTER if your version supports dynamic arrays, or use Data > Get Data > From Sheet to merge the Roles Assigned and Safeguarding Requirements tables in Power Query and load the combined list.
Can I use these lookup formulas in a workbook created in Microsoft Excel?
Yes. WPS Spreadsheets opens and saves .xlsx files and supports the same lookup formulas, so you can edit the workbook without converting it.
