Function Problems

How to Return Multiple Matching Rows in Excel Using FILTER Instead of XLOOKUP

Maira Mehtab Sep 20, 2026 872 views

Question details

The user needs to extract all rows matching specific statuses but finds that the current lookup formula only pulls the first match.

Product: Spreadsheets
Device & OS: not provided
Scenario: Extracting multiple records with statuses like 'Missing' or 'Incomplete-Issues Outstanding' from a dataset into a separate location.
Observed behavior: The XLOOKUP formula only returns the first matching row and inappropriately spills unrelated rows when copied down, rather than returning every matching record.

Before you start

Ensure your dataset does not contain merged cells within the ranges you plan to filter, as they can cause array formula errors, and verify the exact spelling of your status criteria to guarantee accurate matching.

Solution 1Recommended

Use the FILTER Function to Return All Matching Rows

Replace XLOOKUP with the FILTER function, which is designed to evaluate an array against a logical condition and spill all matching records into adjacent cells.

XLOOKUP is a powerful tool, but its fundamental limitation is that it stops searching and returns data as soon as it finds the first match. To retrieve a complete list of all matching records, you must use the FILTER function. FILTER dynamically extracts every row that meets your criteria without the need to manually copy the formula down.

Select the Output Cell

Click the top-left cell where you want the multiple matched results to appear. Ensure there is enough empty space below and to the right so the array can spill out without encountering a #SPILL! error.

Enter the FILTER Formula

Type the FILTER formula using your data range and criteria. For example, to find all rows where the status in column E is 'Missing', use a structure like =FILTER(B14:D47, E14:E47="Missing", "").

Combine Columns if Needed

If you need to concatenate text from different columns (as attempted in the original formula), you can combine ranges in your return array using the ampersand (&). For example: =FILTER(B14:B47 & " " & D14:D47, E14:E47="Missing", "").

Handle Multiple Criteria

To extract a second status like 'Incomplete-Issues Outstanding', you can either create a second FILTER formula in another location, or combine criteria using the plus sign (+) for OR logic: =FILTER(A2:D50, (E2:E50="Missing")+(E2:E50="Incomplete-Issues Outstanding"), "").

Dynamic Arrays Availability: FILTER is a dynamic array function available in Microsoft 365, Excel 2021, and modern spreadsheet software like WPS Office. It automatically spills results, so you do not need to press Ctrl+Shift+Enter.

Advanced Spreadsheets Software

Easily Manage Dynamic Arrays and Filters with WPS Spreadsheet

WPS Office fully supports advanced dynamic array formulas, including FILTER and XLOOKUP, allowing you to instantly extract multiple matching rows from massive datasets without lag.

1. Open Your Workbook: Download and install WPS Office, then open your .xlsx dataset using WPS Spreadsheet.
2. Apply the FILTER Function: Select an empty cell, type your =FILTER(return_array, lookup_array="Criteria", "") formula, and press Enter.
3. View Spilled Results: WPS Spreadsheet will instantly calculate and spill all matching rows dynamically down the column.

Fully compatible with Microsoft Excel formulas and .xlsx filesNative support for dynamic arrays like FILTER, SORT, and UNIQUELightweight application that runs smoothly on older devicesCompletely free core spreadsheet features with a familiar interface

microsoft office alternative - wps office

Frequently Asked Questions

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

A #SPILL! error occurs when the formula attempts to display multiple matched rows, but one or more cells in the downward output range contain data, spaces, or formatting. Clear the cells in the spill path to allow the formula to expand.

Is there any way to make XLOOKUP return multiple values?

Natively, XLOOKUP is designed to return only a single match (the first match from the top, or the last match if searching bottom-to-top). It cannot inherently spill multiple matching records, which is why the FILTER function is the standard replacement for this scenario.

How do I sort the results returned by the FILTER function?

You can wrap your FILTER formula inside the SORT function. For example, using =SORT(FILTER(A2:C20, B2:B20="Missing", ""), 1, 1) will output all matching records and automatically sort them based on the first column in ascending order.

What if no rows match my criteria in the FILTER function?

The FILTER function includes an optional third argument called '[if_empty]'. You can supply a text string here, such as =FILTER(A1:B10, A1:A10="Done", "No matches found"), so it returns a clean message instead of a #CALC! error when nothing is found.

Excel & Data Problems