Function Problems

How to Use Excel Formulas to Return Multiple Matches for Two Criteria

Khadija Khan Sep 29, 2026 869 views

Question details

The user needs to retrieve all corresponding data entries that meet two distinct conditions simultaneously, rather than stopping at the first match.

How to Return Multiple Matches for Two Criteria in Excel

Product: Excel
Device & OS: not provided
Scenario: Filtering and compiling names from a master sheet based on dual criteria, such as a specific job title and a specific region.
Observed behavior: Basic lookup functions like VLOOKUP or traditional INDEX and MATCH formulas only return the first matching instance in a dataset, leaving out other valid matches.

Before you start

Ensure your dataset is organized in clear columns (e.g., Names, Job Titles, Regions) and verify that your spreadsheet software supports dynamic array functions like FILTER and TEXTJOIN.

Solution 1Recommended

Combine FILTER, TEXTJOIN, and IFERROR Functions

This is the most efficient and modern way to extract and concatenate all matches that meet multiple conditions into a single cell.

By nesting the FILTER function inside TEXTJOIN, you can retrieve all matching items as an array and combine them with a delimiter like a comma. The IFERROR function handles cases where no matches are found.

Select the destination cell

Click on the cell in Sheet 2 (the result sheet) where you want the combined matching names to appear.

Enter the formula

Type the formula: =IFERROR(TEXTJOIN(", ",TRUE,FILTER(Sheet1!A:A,(Sheet1!B:B=Sheet2!A2)*(Sheet1!C:C=Sheet2!B2))),"open") into the formula bar.

Execute and apply

Press Enter to execute the formula. If you have multiple rows of criteria, click the cell, grab the fill handle in the bottom-right corner, and drag it down to fill the rest of the column.

Combine FILTER, TEXTJOIN, and IFERROR Functions

Formula Breakdown: The asterisk (*) acts as a logical AND operator for the two conditions. The TRUE argument in TEXTJOIN ensures empty cells are ignored during concatenation.

Solution 2

Use the GROUPBY Function

For users with the latest spreadsheet versions, GROUPBY offers a direct approach to group data and extract array texts.

Solution 3

Use an INDEX and SMALL Array Formula (Legacy Option)

If your software does not support modern dynamic arrays, use a traditional array formula to list successive matches in separate cells.

Advanced Formulas in WPS

Easily Manage Advanced Formulas with WPS Spreadsheet

WPS Spreadsheet fully supports dynamic arrays and advanced functions like FILTER, TEXTJOIN, and IFERROR out of the box, allowing you to quickly retrieve multiple data matches with high performance and zero compatibility issues.

1. Download and Install: Download WPS Office Free from the official website and follow the installation prompts.
2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing .xlsx or .csv data file.
3. Apply Dynamic Arrays: Use the formula bar to enter modern functions like FILTER and TEXTJOIN; WPS Spreadsheet will process them natively.

Fully compatible with Microsoft Excel formulas and .xlsx files.Free and lightweight, launching in seconds without lagging on large datasets.Intuitive user interface for managing complex datasets and troubleshooting functions.

microsoft office alternative - wps office

Frequently Asked Questions

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

This error occurs when the FILTER function finds no matches for your criteria. Wrapping your formula in IFERROR or using the built-in [if_empty] argument within the FILTER function (e.g., adding "open" or "no match" at the end) will replace the error with your custom text.

Can I use more than two criteria with the FILTER function?

Yes, you can add as many criteria as you need by multiplying additional condition arrays within the FILTER function. For example, add *(Sheet1!D:D=Sheet2!C2) to apply a third condition.

Is TEXTJOIN available in all spreadsheet versions?

TEXTJOIN is a modern function introduced in Excel 2019 and Office 365. It is also fully supported in WPS Spreadsheet. If you are using an older version, you might require VBA or complex array formulas to concatenate multiple values.

What does the asterisk (*) do in the FILTER formula criteria?

In dynamic array formulas, the asterisk acts as the logical AND operator. It multiplies the Boolean values (TRUE/FALSE) of each criteria array. Only rows where both conditions are TRUE (1 * 1 = 1) are returned in the final result.

Excel & Data Problems