Function Problems

Excel Formula to List Names Matching a Specific Criteria

Phi Hung Vo Oct 1, 2026 868 views

Question details

Extract a list of names or items (like salespeople) from a dataset based on a specific numerical or text criteria in an adjacent column.

How to List Names Matching a Specific Criteria in Excel

Product: Excel
Device & OS: not provided
Scenario: Filtering a dataset to display a specific subset of names, such as salespeople who have sold exactly seven apples.
Observed behavior: Needs a formula or automated method to return multiple corresponding names that match a single defined condition.

Before you start

Ensure your data ranges are consistent in size and note that the FILTER function is only available in Microsoft 365, Excel 2021, or compatible modern spreadsheet software like WPS Office.

Solution 1Recommended

Use the Dynamic FILTER Function

The fastest and most dynamic method for modern Excel versions to extract a list of names matching your criteria.

The FILTER function can dynamically return an array of values that meet your exact condition. It automatically updates if your source data changes.

Select the destination cell

Click on an empty cell where you want the top of your extracted list to appear. Make sure there is enough blank space below it for the results to spill into.

Enter the FILTER formula

Type the formula following this syntax: =FILTER(return_range, criteria_range=criteria). For example, if names are in C2:C7 and apples sold are in D2:D7, enter: =FILTER(C2:C7, D2:D7=7)

Handle empty results (Optional)

To avoid a #CALC! error when no one matches the criteria, add a third argument: =FILTER(C2:C7, D2:D7=7, "No match")

Dynamic Arrays: You only need to type the formula in the first cell. The results will automatically 'spill' down into the cells below.

Solution 2

Use the Advanced Filter Feature

Best for users on older versions of Excel (Excel 2019 and older) that do not support dynamic array formulas like FILTER.

Filter and Extract Data Easily in WPS Office

WPS Office Spreadsheet fully supports dynamic array formulas like the FILTER function, allowing you to instantly list names matching your criteria. It provides a lightweight, highly compatible, and cost-effective alternative for your data analysis tasks.

1. Open your data in WPS Spreadsheet: Launch WPS Office and open your workbook containing the dataset.
2. Apply the FILTER formula: Select an empty cell and type =FILTER(C2:C7, D2:D7=7), adjusting the ranges for your specific table.
3. Press Enter: Hit Enter to instantly generate your filtered list of names.

Fully supports the modern FILTER function and dynamic arrays.100% compatibility with Microsoft Excel (.xlsx) file formats.Lightweight software that handles large datasets smoothly.Free to use with a familiar, easy-to-navigate interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting a #SPILL! error with my FILTER formula?

A #SPILL! error occurs when there is existing data, text, or a space in the cells below where you typed the formula, preventing the results from expanding. Clear the cells below your formula to fix this issue.

Can I filter names based on multiple criteria?

Yes. You can use multiplication (*) for AND logic or addition (+) for OR logic in the FILTER function. For example, to find salespeople who sold 7 apples AND are in Region A: =FILTER(C2:C7, (D2:D7=7)*(E2:E7="Region A")).

How do I return a blank instead of a #CALC! error if no names match?

You can utilize the optional third argument in the FILTER function called [if_empty]. By putting two double quotes "" at the end of your formula (e.g., =FILTER(C2:C7, D2:D7=7, "")), Excel will return a blank cell instead of an error when no matches are found.

Excel & Data Problems