Function Problems

How to Return Multiple Matching Values from an Excel Lookup

Aamir Naveed Akram Oct 1, 2026 868 views

Question details

The user needs to retrieve every matching result from a lookup operation in Excel, rather than just the first match, and output them into separate cells or combined within a single cell.

How to Return Multiple Matching Values from an Excel Lookup

Product: Microsoft Excel
Device & OS: not provided
Scenario: Performing advanced data lookups across worksheets where a single lookup value has multiple corresponding entries in the source data.
Observed behavior: Standard lookup functions like VLOOKUP only return the first found match, preventing the user from viewing all relevant data points associated with the lookup criteria.

Before you start

Ensure you are using a version of Excel that supports dynamic array functions (such as Microsoft 365 or Excel 2021 and later), as the methods below rely on the FILTER function to extract multiple matches.

Solution 1Recommended

Use the FILTER Function to Spil Matches into Separate Cells

The FILTER function is the most efficient way to look up a value and return all corresponding matches into separate, adjacent cells dynamically.

Dynamic array formulas allow a single formula to return values across multiple cells. The FILTER function searches the source array and automatically "spills" every matched record downwards or across.

Select the destination cell

Click on the empty cell where you want the first matching result to appear. Make sure there is enough empty space below it for the results to spill.

Enter the FILTER formula

Type the formula: =FILTER(Source!B:B, Source!A:A=D2, "No match"). In this formula, Source!B:B is the column with the values you want to return, Source!A:A is the column to search, and D2 is the cell containing your lookup value.

Apply the formula

Press Enter. All matching results will immediately populate in separate cells below your selected destination.

Use the FILTER Function to Spil Matches into Separate Cells

Handling Empty Results: The "No match" argument at the end of the formula prevents Excel from displaying a #CALC! error if the lookup value isn't found in your source data.

Solution 2

Combine Multiple Matches into a Single Cell with TEXTJOIN

If you prefer to keep your worksheet compact by placing all multiple matching results into a single cell, you can nest the FILTER function inside a TEXTJOIN function.

Easily Manage Data Lookups

Use WPS Spreadsheet for Advanced Array Functions

WPS Spreadsheet fully supports advanced dynamic array functions like FILTER and TEXTJOIN. You can effortlessly lookup and extract multiple matching values with formulas completely compatible with your existing workbooks.

1. Open your workbook in WPS: Launch WPS Spreadsheet and open your existing data file.
2. Input the FILTER formula: Select the target cell and type =FILTER(B:B, A:A=D2) to look up your data.
3. Press Enter to spill matches: Hit Enter, and WPS Spreadsheet will instantly display all matching values in the cells below.

Full compatibility with Microsoft Excel formats (.xlsx and .xls).Native support for modern dynamic array formulas to simplify complex data tasks.Free, lightweight software with a highly familiar user interface for seamless migration.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use VLOOKUP to return multiple values?

No, the standard VLOOKUP function is strictly designed to stop at the first match it finds. To return multiple values, you must use the FILTER function, or a combination of INDEX and AGGREGATE functions if you are on an older version of Excel.

Why is my FILTER formula returning a #CALC! error?

A #CALC! error generally occurs when the FILTER function finds zero matches and you haven't provided an 'if_empty' argument. You can resolve this by adding a fallback string, like "No match" or "", as the third parameter in your FILTER formula.

How do I filter based on multiple conditions?

You can apply multiple criteria in the FILTER function by using multiplication for AND logic or addition for OR logic. For example: =FILTER(B:B, (A:A=D2)*(C:C="Active")) will return values where both conditions are met.

Why am I getting a #SPILL! error when using the FILTER function?

A #SPILL! error means that the dynamic array formula needs to output results into multiple adjacent cells, but one or more of those destination cells are not empty. Clear the blocking data in the way, and the formula will automatically populate.

Excel & Data Problems