Excel Formula to Match Employee Roles and Training Requirements
Question details
The user needs to create an Excel training matrix that matches employees to their job roles and compares their required functional training across multiple worksheets.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Building a cross-referenced employee training matrix to map specific job roles to required functional training modules located on different sheets.
- Observed behavior
- Requires a reliable formula or data modeling method to accurately pull, compare, and display "X" marks for training requirements based on matching employee names and assigned roles.
Ensure your employee list, roles, and training requirements are organized in clear, consistent tables across your worksheets, and verify that employee names and roles are spelled exactly the same on every sheet.
Use SUMPRODUCT, INDEX, and MATCH Formulas
Combine these lookup functions to dynamically check for intersecting training requirements across multiple sheets and automatically output an 'X'.
This method evaluates two separate arrays using INDEX and MATCH to locate the role and employee requirements, then multiplies them within a SUMPRODUCT function to act as an AND logic check.
Organize your workbook so that 'Sheet 1' contains the job roles and required functional training, while 'Sheet 2' contains the employees and their assigned roles.
Click on the first blank cell in your main training matrix worksheet where you want the training indicator (e.g., an 'X') to appear.
Type the following formula into the formula bar: =IF(SUMPRODUCT((INDEX('Sheet 1'!$B$2:$D$4,MATCH(B$1,'Sheet 1'!$A$2:$A$4,0),)="X")*(INDEX('Sheet 2'!$B$2:$D$5,MATCH($A2,'Sheet 2'!$A$2:$A$5,0),)="X")),"X","")
Press Enter to apply the formula. Click the small square at the bottom-right corner of the cell and drag it across the columns and down the rows to populate the rest of the matrix.
Utilize Power Query for Advanced Data Modeling
If you are using Microsoft 365 on a Windows device, Power Query and Power Pivot offer a robust alternative for merging and analyzing relationship-based tables.
Manage Training Matrices Easily with WPS Spreadsheet
WPS Spreadsheet fully supports complex array formulas like SUMPRODUCT, INDEX, and MATCH, allowing you to build and maintain comprehensive employee training matrices completely for free.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your employee and training data.
- 2. Apply the lookup formula: Select the target cell in your matrix and paste the combined SUMPRODUCT, INDEX, and MATCH formula into the formula bar.
- 3. Drag to fill: Press Enter, then drag the fill handle across your matrix to instantly evaluate the training requirements for all employees.
- 4. Save securely: Save your finished training matrix in the standard .xlsx format to ensure seamless sharing with your team.

Frequently Asked Questions
Why is my INDEX MATCH formula returning an #N/A error?
An #N/A error occurs when the MATCH function cannot find the exact lookup value. Check for hidden leading or trailing spaces in your employee names or job roles, and ensure the lookup ranges cover the exact same dimensions.
Can I use XLOOKUP instead of INDEX and MATCH for this matrix?
Yes, if your spreadsheet software supports XLOOKUP, you can use it to simplify the syntax. However, because this specific matrix requires checking intersecting arrays (two conditions resulting in an 'X'), you will still need to wrap the XLOOKUP functions within an AND logic check or a SUMPRODUCT formula.
How do I highlight employees who are missing their required training?
You can use Conditional Formatting to visualize missing training. Select your matrix cells, go to 'Home' > 'Conditional Formatting' > 'Highlight Cells Rules', and set a rule to highlight blank cells or cells lacking an 'X' in a specific color like red.




