How to Return Every Matching Row in Excel (Instead of One VLOOKUP Result)
Question details
The user needs a method to retrieve all rows that match a specific condition (such as an 'Active' status) rather than just the first match returned by standard lookup formulas.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Extracting multiple records based on a shared status or criterion from a large dataset to a new location or designated sheet.
- Observed behavior
- Standard lookup functions like VLOOKUP and XLOOKUP only return the first matching result they encounter, leaving out other valid matching rows in the dataset.
Ensure you are using a version of your spreadsheet software that supports Dynamic Array formulas, as this provides the easiest and most automated method for retrieving multiple matching rows.
Use the Dynamic Array FILTER Function
The FILTER function is the most efficient and modern way to extract multiple matching rows based on a specific criteria without manual workarounds.
Unlike VLOOKUP, which stops searching at the first match, the FILTER function evaluates an entire array and returns all rows that meet your condition. This creates a dynamic spill range that updates automatically whenever your source data changes, making it ideal for tracking statuses like 'Active' or 'Discharged' across different sheets.
Click on the top-left cell in your destination worksheet or area where you want the extracted records to appear.
Type =FILTER(array, include, [if_empty]). For example, type =FILTER(A2:F100, B2:B100="Active", "No matches found") where A2:F100 is your data table and B2:B100 contains the status column.
Hit Enter. The formula will automatically spill down and across to display every row that matches your specified status.
Extract Rows Using the Advanced Filter Feature
If you do not have access to dynamic array functions, the Advanced Filter tool provides a built-in way to copy all matching rows to a new location.
Easily Extract Multiple Matches with WPS Spreadsheet
WPS Spreadsheet fully supports modern dynamic array functions like FILTER, making it simple to pull multiple matching rows without complex legacy workarounds. Process your data effortlessly with a familiar, easy-to-use interface.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your workbook containing the source data that needs filtering.
- 2. Input the FILTER formula: Select an empty cell in your desired output sheet and enter your =FILTER() formula to target your data range and criteria.
- 3. View all matching rows instantly: Press Enter to see all matching rows seamlessly populate your spreadsheet.

Frequently Asked Questions
Why does VLOOKUP only return the first match?
By design, the VLOOKUP function searches a column from top to bottom and completely stops at the very first instance that matches the lookup value. It is not programmed to scan the rest of the dataset for additional matches, which is why array functions like FILTER are needed for multiple results.
Can I use XLOOKUP to return every matching row?
No, XLOOKUP also defaults to returning a single matching item. While it offers the flexibility to search from last-to-first or first-to-last, it cannot independently spill multiple matching rows. You must use the FILTER function to achieve this.
How do I filter multiple matching rows based on multiple criteria?
You can evaluate multiple conditions in the FILTER function by multiplying them together. For example, using =FILTER(A2:F100, (B2:B100="Active")*(C2:C100="Sales")) will return only the rows where the status is 'Active' AND the department is 'Sales'.
What if my spreadsheet version doesn't support the FILTER function?
If you are using an older spreadsheet version that lacks dynamic arrays, you can use the built-in Advanced Filter tool under the Data tab. Alternatively, you can upgrade to a modern application like WPS Office, which is free and fully supports modern dynamic array functions.




