Function Problems

How to Generate Lists from an Excel Role and Rights Matrix

Maira Mehtab Sep 28, 2026 869 views

Question details

The user wants to generate dynamic lists from an access matrix to return all rights assigned to a specific role, or all roles assigned to a specific right.

Product: Excel
Device & OS: not provided
Scenario: Managing user access controls and dynamically extracting roles and permissions from a structured access matrix.
Observed behavior: The user aims to output a filtered array of roles or rights based on a drop-down selection from a matrix table without using complex VBA.

Before you start

Ensure your Excel or WPS Spreadsheet version supports dynamic array functions like FILTER and XMATCH, and verify that your matrix layout clearly separates role headers and rights labels.

Solution 1Recommended

Extract Rights for a Specific Role

Use a combination of FILTER, INDEX, and XMATCH to return a list of rights assigned to a chosen role.

This formula extracts vertical list elements based on horizontal headers. It checks the column of your chosen role in the matrix, filtering out any blank cells to return only the associated rights.

Set up the data ranges

Ensure your roles are organized in a top row (e.g., C2:H2), rights in a side column (e.g., B3:B9), and the assignment marks within the matrix body (C3:H9).

Select the target cell

Click on the cell where you want to output the list of rights for a specific role (for example, B14).

Enter the dynamic array formula

Type the formula =FILTER(B3:B9,INDEX(C3:H9,0,XMATCH(B13,C2:H2))<>"","") and press Enter, where B13 is the cell containing your chosen role selection.

Handling Blank Cells: The condition <>"" ensures that the formula only captures rights that have a specific mark (like an 'X' or checkmark) in the matrix body.

Solution 2

Extract Roles for a Specific Right

Use TRANSPOSE alongside FILTER, INDEX, and XMATCH to list all roles that possess a specific right.

Advanced Spreadsheet Tool

Easily Manage Matrix Data with WPS Spreadsheet

WPS Spreadsheet fully supports dynamic array formulas, allowing you to instantly generate lists from your role and rights matrix without complex coding.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the access matrix.
2. Create a drop-down menu: Use Data Validation to create a drop-down list of your roles or rights for easier selection.
3. Apply the array formulas: Input the FILTER, INDEX, and XMATCH formula in your desired output cell.
4. Auto-populate results: Press Enter, and WPS Spreadsheet will automatically spill the extracted lists into the adjacent empty cells.

100% compatible with Microsoft Excel formulas like FILTER and XMATCHFree and lightweight spreadsheet softwareSupports cross-sheet references for matrix dataIntuitive interface for managing complex data sets

microsoft office alternative - wps office

Frequently Asked Questions

Can I place the matrix data on a different worksheet?

Yes, you can reference data on another sheet by adding the sheet name before the cell ranges in your formula, such as 'Matrix Data'!C2:H2. This helps keep your reporting dashboard visually clean.

Why does my formula return a #NAME? error?

This error occurs if your spreadsheet software version does not support newer dynamic array functions like FILTER and XMATCH. Ensure you are using a modern version of Excel (Microsoft 365) or a recent version of WPS Office.

How does the matrix filter blank cells?

The formula uses the condition <>"" to ignore empty cells. Ensure that valid role assignments are marked with a character (like 'X' or 'Yes') inside the matrix so they are successfully filtered.

Excel & Data Problems