How to Find People by Role Across Multiple Excel Columns (FILTER & BYROW)
Question details
The user wants to retrieve a list of people who hold a specific role when the role data is spread across multiple columns in an Excel dataset.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Extracting or filtering a master list of individuals based on a selected role (often chosen from a dropdown menu) that might appear in any of several assigned role columns.
- Observed behavior
- Users need an automated formula to pull these matching records into a single dynamic view without manually filtering each individual role column.
Ensure your dataset is organized in a tabular format without merged cells, and verify that you are using a modern spreadsheet version (like Excel 365, Excel 2021, or the latest WPS Office) that supports dynamic array functions such as FILTER and BYROW.
Use the FILTER and BYROW Functions
This is the cleanest and most efficient method to filter rows where a specific value exists in any of the specified columns across an array.
By utilizing the BYROW function combined with LAMBDA and XMATCH, you can evaluate each row of your role columns simultaneously. If the selected role is found anywhere in that row, FILTER will return the entire record.
Create a data validation dropdown in a cell (e.g., D2) containing the list of roles you want to search for. This makes your search dynamic.
In the upper-left cell where you want your results to appear, enter the formula: =FILTER(ROLES!A1:L90, BYROW(ROLES!C1:K90, LAMBDA(r, ISNUMBER(XMATCH(D2, r)))), "none")
Modify ROLES!A1:L90 to your entire data range, ROLES!C1:K90 to the specific range containing the role columns, and D2 to your dropdown cell.
Use the FILTER and MMULT Functions
A robust mathematical alternative for searching across multiple columns if you prefer matrix multiplication logic over LAMBDA functions.
Easily Manage Complex Excel Data with WPS Spreadsheet
WPS Spreadsheet offers full support for advanced dynamic array formulas like FILTER, MMULT, and sequence logic, making it incredibly easy to extract and analyze data across multiple columns.
- 1. Open your workbook: Launch WPS Spreadsheet and open your existing Excel (.xlsx) file containing the role data.
- 2. Apply the FILTER formula: Select the destination cell and input the exact =FILTER(..., BYROW(...)) dynamic array formula used in your Excel workflow.
- 3. Format your results: Use WPS Spreadsheet's built-in formatting tools to present the extracted list cleanly.
Frequently Asked Questions
What happens if the selected role is not found in any column?
In both the BYROW and MMULT formulas provided, the final argument is "none". If the role is not found in the search range, the FILTER function will return the text "none". To leave it blank instead, change "none" to "".
Why is my FILTER formula returning a #NAME? error?
A #NAME? error typically indicates that your version of Excel or spreadsheet software does not support dynamic array functions like FILTER, BYROW, or LAMBDA. These functions require Excel 365, Excel 2021, or an updated version of WPS Office.
Why use a dropdown on one sheet instead of using separate tabs for each role?
Using a single master sheet with a dropdown cell (e.g., cell D2) is significantly easier to maintain. It prevents duplicating data and formulas across dozens of sheets, reduces workbook file size, and ensures all updates happen in one centralized view.
Can I search for multiple roles at the same time using this method?
Yes, but you will need to expand the criteria logic. Instead of checking for equality to a single cell, you can use functions like ISNUMBER(MATCH()) combined with boolean addition (+) inside the FILTER criteria to evaluate against an array of multiple roles.
