Function Problems

How to Filter Excel Rows When a Name Appears in Multiple Columns

Phi Hung Vo Oct 9, 2026 868 views

Question details

The user needs to filter and extract entire rows from a dataset where a specific name (like a staff member) is present in one of several different role columns.

How to Filter Excel Rows When a Name Appears in Multiple Columns

Product: Microsoft Excel
Device & OS: not provided
Scenario: Displaying all projects associated with a selected person when their name could appear under various columns, avoiding manual filtering for each individual column.
Observed behavior: The user wants an automated, dynamic formula or query to return matching rows seamlessly instead of relying on complex manual filtering.

Before you start

Verify that you are using a modern spreadsheet version (like Microsoft 365, Excel 2021, or the latest WPS Office) that supports dynamic array functions such as FILTER, BYROW, and LAMBDA.

Solution 1Recommended

Use the FILTER, BYROW, and LAMBDA Functions

This is the most efficient and dynamic method to extract rows based on a lookup value spanning multiple columns without altering the original dataset.

By combining the FILTER function with BYROW and LAMBDA, you can evaluate multiple columns in a row simultaneously. The BYROW function goes through the dataset row by row, and LAMBDA checks if the target name exists in that specific row.

Set up your target cell

Choose an empty cell, for example A8, and type the exact staff name you want to search for.

Enter the dynamic formula

Select the cell where you want the filtered results to appear. Enter the formula: =FILTER(A2:E6,BYROW(B2:E6,LAMBDA(r,COUNTIF(r,A8)>0)))

Adjust the range references

In the formula, change A2:E6 to match your entire source data range. Change B2:E6 to exactly match the columns where the staff names might appear.

Execute the formula

Press Enter. The formula will automatically spill all rows where the specified name is found in any of the designated columns.

Use the FILTER, BYROW, and LAMBDA Functions

Handling Empty Results: If you want to avoid a #CALC! error when no names match, add a third argument to your FILTER function like this: =FILTER(..., ..., "No matching projects")

Solution 2

Transform Data Using Power Query

Ideal for scalable solutions and larger datasets where you want to reshape the data without relying on complex array formulas.

Solve it instantly with WPS Office

Filter Multiple Columns Dynamically in WPS Spreadsheet

WPS Spreadsheet fully supports advanced array functions and data filtering tools. You can easily manage complex datasets and filter multiple columns using the exact same formulas as Excel, completely free of charge.

1. Open your dataset: Launch WPS Office and open your .xlsx file containing the project and staff data.
2. Apply the array formula: In an empty cell, type the formula =FILTER(A2:E6,BYROW(B2:E6,LAMBDA(r,COUNTIF(r,A8)>0))) and adjust the ranges for your table.
3. View dynamic results: Press Enter to instantly display all rows where the specified name appears in any of the selected columns. The results update automatically if data changes.

100% compatible with Microsoft Excel file formats (.xlsx, .xls)Natively supports advanced dynamic array functions like FILTER, BYROW, and LAMBDALightweight, fast, and features a familiar tabbed user interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does my FILTER formula return a #CALC! error?

The #CALC! error typically occurs when the FILTER function finds no matching records in the dataset. You can fix this by supplying an 'if_empty' value as the third argument in your formula, for example: =FILTER(A2:E6, ..., "No matches found").

Can I filter for multiple names at the same time across different columns?

Yes, you can modify the criteria inside the LAMBDA function to check for multiple names. You can use the addition operator (+) to represent OR logic, wrapping multiple COUNTIF conditions, or match against a list of names.

Are the BYROW and LAMBDA functions available in all Excel versions?

No, BYROW and LAMBDA are dynamic array functions that are only available in Microsoft 365, Excel 2021, and modern spreadsheet applications like WPS Office. If you are using Excel 2019 or older, you will need to rely on helper columns or Power Query.

How do I perform this if my data isn't formatted as an official Excel Table?

The FILTER formula will still work with standard cell ranges (like A1:E100). However, converting your data to an official Table (Ctrl+T) is highly recommended because it turns ranges into dynamic references, ensuring your formula automatically updates when new rows are added.

Excel & Data Problems