How to Return All Matching Results in Excel Instead of the First Match
Question details
The user wants to extract every matching record for a specific criteria, overcoming the limitation of standard lookup functions that only return the first occurrence.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Extracting data from a dataset where multiple entries share the same lookup value, such as multiple drivers having the same arrival time.
- Observed behavior
- The VLOOKUP function stops searching and returns only the very first matching result it finds, ignoring any subsequent matches in the data range.
Verify your Excel version, as dynamic array formulas like FILTER are only available in Microsoft 365 and Excel 2021 or later. For older versions, you will need to rely on Power Query or VBA.
Use the FILTER Function (Microsoft 365 & Excel 2021)
The most efficient way to return multiple matching results in newer versions of Excel is using the dynamic FILTER array function.
Dynamic array formulas have revolutionized how Excel handles data. The FILTER function allows you to specify a range to return and a condition it must meet, automatically spilling all matching results into adjacent cells without complex nested formulas.
Click on an empty cell where you want the first matching result to appear. Ensure there is enough empty space below it for the results to spill.
Type the formula =FILTER(return_array, lookup_array=lookup_value). For example, to return driver names (Column B) based on a specific arrival time in cell E1 (Column A), type =FILTER(B2:B100, A2:A100=E1).
Press Enter. Excel will automatically extract and display every matching record in a vertical array.
Extract Multiple Matches Using Power Query
Ideal for larger datasets and repeatable data transformations, Power Query works across most modern Excel versions, including older ones like Excel 2016.
Create a Custom VBA Function for Older Excel Versions
If you are using an older version of Excel (like Excel 2013 or 2016) and prefer a formula-based approach over Power Query, you can create a custom VBA User Defined Function (UDF).
Use WPS Spreadsheet to Easily Filter and Extract Multiple Matches
WPS Office offers robust data handling capabilities, fully supporting dynamic arrays and lookup functions like FILTER. You can extract all matching results instantly without relying on complex workarounds.
- 1. Open your workbook in WPS: Launch WPS Office and open your spreadsheet containing the source data.
- 2. Select the target cell: Click on the blank cell where you want your multiple matched results to begin spilling.
- 3. Input the FILTER formula: Type the formula =FILTER(Return_Range, Criteria_Range="Your Match Criteria").
- 4. View all matches instantly: Press Enter. WPS Spreadsheet will instantly process the array and return all matching rows perfectly formatted.
Frequently Asked Questions
Why does VLOOKUP only return the first match?
VLOOKUP is designed by default to scan a column from top to bottom. As soon as it finds the first exact or approximate match that satisfies the condition, it retrieves the corresponding value and stops calculating, ignoring any subsequent matches.
Can I return multiple matching results in a single cell separated by commas?
Yes. In modern versions of Excel, you can combine TEXTJOIN with the FILTER function. Use a formula like =TEXTJOIN(", ", TRUE, FILTER(return_range, criteria_range=criteria)) to bundle all matching results into one single cell.
Does XLOOKUP return multiple matches?
By default, XLOOKUP also returns a single match (the first or last, depending on your search mode). While it can return multiple columns for a single matching row, it cannot natively return multiple rows of matches. The FILTER function is the correct choice for returning multiple rows.
Is the FILTER function available in Excel 2016 or 2019?
No, the FILTER function is a dynamic array formula introduced in Microsoft 365 and Excel 2021. If you are using Excel 2016 or 2019, you must use Power Query, VBA, or complex INDEX and AGGREGATE array formulas to return multiple results.
