How to Use Excel FILTER Formula to Match Names Across Multiple Columns
Question details
The user needs to extract rows from a dataset that meet a specific condition while also finding a specific name that might appear in any of several columns.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Filtering a large dataset based on multiple criteria where the target text (such as a person's name) can exist in varying columns.
- Observed behavior
- The user wants to successfully return all relevant rows without manually checking each column, utilizing advanced array filtering techniques rather than traditional filters.
Ensure your dataset is well-organized with clear column headers, and verify that the version of Excel or WPS Spreadsheet you are using supports Dynamic Array functions like FILTER.
Combine Criteria Using Arithmetic Operators in FILTER
Use the multiplication operator (*) for AND logic and the addition operator (+) for OR logic to check multiple columns simultaneously.
In dynamic array formulas, standard logical functions like AND/OR cannot be used because they aggregate the array into a single result. Instead, you use arithmetic operators to evaluate criteria row by row.
Click on the empty cell where you want the filtered results to begin.
Enter the formula structure using * for mandatory conditions and + for alternative conditions: =FILTER(array, (condition1) * ((condition2) + (condition3)), "if_empty").
For example, to find rows where status is 'Out for Approval' AND the name 'Wole.Bankole' appears in column F OR column M, type: =FILTER(A1:S75, (E1:E75="Out for Approval") * ((F1:F75="Wole.Bankole") + (M1:M75="Wole.Bankole")), "Nothing").
Press Enter to generate the dynamic array of filtered rows.
Create a Helper Column with COUNTIF for Many Searchable Columns
When checking across many columns, writing out every column addition can be tedious. A COUNTIF helper column simplifies the FILTER formula.
Apply the MMULT Function for Matrix Array Filtering
You can nest the MMULT function inside FILTER to evaluate multiple columns in a single array formula without needing an extra helper column.
Master Dynamic Array Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports dynamic array functions like FILTER, making it incredibly easy to manage complex datasets and extract rows based on multi-column criteria without lag.
- 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing the data you want to filter.
- 2. Select the destination: Click the cell where the filtered array should start displaying its results.
- 3. Enter the formula: Type your multi-column FILTER formula, utilizing the plus sign (+) for OR logic and the asterisk (*) for AND logic.
- 4. View spilled results: Press Enter, and WPS Spreadsheet will instantly spill the filtered results into the adjacent cells below and to the right.
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 and the [if_empty] argument is omitted. To fix this, always include a fallback value, such as "Nothing" or "No records found" at the end of your formula: =FILTER(range, criteria, "No records found").
Can I use the OR function instead of the plus sign (+) inside FILTER?
No, the standard OR and AND functions aggregate arrays into a single TRUE/FALSE result, which prevents the FILTER function from evaluating row-by-row. You must use the plus sign (+) for OR logic and the asterisk (*) for AND logic in dynamic arrays.
Why is my FILTER array returning a #SPILL! error?
A #SPILL! error means the area where the dynamic array needs to display its results is blocked by existing data, spaces, or formatting. Clear the cells below and to the right of your formula to allow the results to spill properly.
