Formula Errors

Excel Formula to Match Employee Roles and Training Requirements

Algirdas JasaitisAlgirdas Jasaitis Nov 3, 2026 869 views

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.

How to Create an Excel Formula to Match Employee Roles and Training Requirements
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.
Before you start

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.

Solution 1Recommended

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.

1
Prepare your worksheets

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.

2
Select the target cell

Click on the first blank cell in your main training matrix worksheet where you want the training indicator (e.g., an 'X') to appear.

3
Enter the matrix formula

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","")

4
Apply and fill

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.

Adjust Cell References: Ensure you adjust the sheet names ('Sheet 1', 'Sheet 2') and the absolute cell references (like $B$2:$D$4) to match the exact size and layout of your actual data ranges.
Efficient Data Management

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. 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your employee and training data.
  2. 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. 3. Drag to fill: Press Enter, then drag the fill handle across your matrix to instantly evaluate the training requirements for all employees.
  4. 4. Save securely: Save your finished training matrix in the standard .xlsx format to ensure seamless sharing with your team.
100% compatibility with Microsoft Excel .xlsx files and advanced formulasLightweight architecture ensures smooth performance even with massive HR datasetsIntuitive interface makes troubleshooting and auditing complex matrix formulas simple
microsoft office alternative - wps office

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.