Function Problems

How to Return All Matching Values Instead of One XLOOKUP Result

Amos Gikunda Oct 21, 2026 868 views

Question details

The user needs to retrieve every matching row and multiple columns from a dataset for a specific lookup value, overcoming the limitation of XLOOKUP which only returns the first matching result.

How to Return All Matching Values Instead of the First XLOOKUP Result

Product: Spreadsheet
Device & OS: not provided
Scenario: Extracting all matching records from a large dataset based on a specific criteria, rather than just pulling the first occurrence.
Observed behavior: XLOOKUP stops searching after finding the first matching result, failing to extract the remaining rows that also meet the lookup condition.

Before you start

Ensure your dataset is organized in standard columns without merged cells, and verify that your spreadsheet application supports modern dynamic array functions like FILTER.

Solution 1Recommended

Use the FILTER Function to Extract All Matching Rows

The FILTER function is the ideal solution for extracting multiple matching records, as it dynamically spills all rows that meet your criteria into the worksheet.

While XLOOKUP is excellent for finding a single exact match, Excel's dynamic array capability allows the FILTER function to return a subset of your data matching your exact conditions, spanning multiple rows and columns.

Select the destination cell

Click on an empty cell where you want the upper-left corner of your extracted results to appear. Make sure there is enough empty space below and to the right for the data to spill into.

Input the FILTER formula

Type `=FILTER(return_array, lookup_array=lookup_value, "If empty")`. For example, type `=FILTER(B2:D100, A2:A100=F2, "No matches")` where B2:D100 is the data you want to output, A2:A100 is the column you are searching, and F2 contains your specific lookup criteria.

Execute the formula

Press Enter. The function will instantly retrieve every row matching your lookup value and spill the multiple columns dynamically across your worksheet.

Use the FILTER Function to Extract All Matching Rows

Dynamic Updates: Because FILTER is a dynamic array function, any changes or additions to your original source data will automatically update the spilled results in real time.

Advanced Formulas in WPS

Easily Extract Multiple Matches Using WPS Spreadsheet

WPS Spreadsheet fully supports dynamic array formulas including the FILTER function, allowing you to return all matching values effortlessly. It offers exceptional compatibility with standard spreadsheet functions for sophisticated data analysis.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open the workbook containing the source data you want to search.
2. Select a blank output cell: Click on the cell where you want your multiple lookup results to begin displaying.
3. Apply the FILTER function: Enter `=FILTER(B2:D100, A2:A100=F2, "No records found")` to instruct WPS Spreadsheet to grab all matching rows.
4. View your dynamic results: Press Enter, and watch WPS Spreadsheet automatically spill all relevant records instantly without needing to drag the formula down.

Fully compatible with Microsoft Excel formulas, including XLOOKUP and dynamic arrays like FILTER.Seamlessly handles large datasets with fast formula execution and dynamic spilling.Free and lightweight office suite with an intuitive interface for efficient data extraction.

microsoft office alternative - wps office

Frequently Asked Questions

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

A #CALC! error typically occurs when the FILTER function finds no matches and the [if_empty] argument has not been provided. You can resolve this by adding a fallback string at the end of your formula, such as `=FILTER(A1:B10, A1:A10="Value", "No matches found")`.

Can I use multiple criteria to return matching values?

Yes, you can apply multiple criteria using the FILTER function by multiplying conditions together. For example, `=FILTER(A2:C10, (A2:A10="Criteria1")*(B2:B10="Criteria2"), "No matches")` ensures both conditions must be met to return the matching rows.

Why does my formula show a #SPILL! error instead of the results?

A #SPILL! error happens when the destination range where the results are supposed to populate is not entirely blank. If there are existing values, merged cells, or even invisible space characters blocking the output area, the formula will fail. Clear the blocking cells to allow the formula to display all matches.

Excel & Data Problems