Formula Errors

Excel Formula to Return Names When Multiple Criteria Match

Steve K Oct 12, 2026 869 views

Question details

The user needs an Excel formula to dynamically extract and return "Reported By" names from a dataset based on partial text matches for a specific person, while excluding rows marked as "No issues".

How to Return Names in Excel Based on Multiple Criteria Matching

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Filtering a dataset to extract a list of names based on a combination of inclusion (partial text match of a name) and exclusion (ignoring specific status labels) criteria.
Observed behavior: The goal is to generate a dynamic list of names that instantly updates based on the target search name and the issue status.

Before you start

Ensure your version of Excel or WPS Spreadsheet supports dynamic array formulas, as the FILTER function requires this feature to automatically spill results into adjacent cells.

Solution 1Recommended

Use FILTER, ISNUMBER, and SEARCH to Extract Matching Names

Combine the FILTER function with ISNUMBER and SEARCH to filter rows by a partial text match while simultaneously excluding specific unwanted statuses using array multiplication.

The FILTER function is perfect for extracting data based on criteria. By multiplying two conditions together with the asterisk (*), you create an AND logic gate. The ISNUMBER(SEARCH()) combination checks if the target name exists anywhere within the text string, allowing for partial matches.

Select the destination cell

Click on an empty cell (e.g., G2) where you want the top of your dynamically filtered list of names to appear.

Enter the FILTER formula

Type the formula: =FILTER(B2:B7, ISNUMBER(SEARCH(E2, B2:B7)) * (C2:C7<>"No issues")). In this formula, B2:B7 is the range of names to return, E2 contains the name to search for, and C2:C7 is the issue status column.

Apply and view results

Press Enter. The formula will evaluate both conditions and automatically spill the matching names down the column.

Use FILTER, ISNUMBER, and SEARCH to Extract Matching Names

Dynamic Array Spilling: Make sure there are empty cells below your target cell. If there is existing data in the way, the formula will return a #SPILL! error.

Seamlessly Handle Complex Formulas

Filter and Analyze Data Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array formulas like FILTER, ISNUMBER, and SEARCH, allowing you to extract and analyze complex datasets with ease.

1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing the data you want to filter.
2. Select the output cell: Click on the specific cell where the filtered list of names should begin.
3. Apply the advanced formula: Type your FILTER and SEARCH formula and press Enter to instantly extract the matching data.

Fully compatible with Microsoft Excel formula syntaxSupports advanced dynamic array functions for instant data extractionLightweight and fast, even when processing large datasetsFree to download and use for everyday productivity tasks

microsoft office alternative - wps office

Frequently Asked Questions

What should I do if the FILTER function returns a #CALC! error?

The #CALC! error occurs when the FILTER function finds no matching records based on your criteria. You can prevent this by adding a final argument to the function, such as =FILTER(range, criteria, "No match found"), which will display your custom message instead of an error.

Why use ISNUMBER and SEARCH instead of an exact match?

Using ISNUMBER(SEARCH()) allows for a partial text match (it is not case-sensitive). If a cell contains a longer string but includes the person's name (e.g., 'Reported by John Doe'), this combination will successfully identify and match it, which an exact equals (=) operator cannot do.

Can I add a third condition to this filtering formula?

Yes, you can add more conditions by simply multiplying another criteria array. For example, add *(D2:D7="Completed") inside the FILTER criteria argument to enforce a third requirement where all three conditions must be met.

Why am I getting a #SPILL! error when I press Enter?

A #SPILL! error means the dynamic array formula does not have enough empty space below the entry cell to display all the resulting names. Clear any text or data in the cells directly beneath your formula to allow the results to populate properly.

Excel & Data Problems