Function Problems

Return All Matching Rows in Excel with FILTER

Amos GikundaAmos Gikunda Nov 18, 2026 869 views

Question details

Return every row in an Excel table or range that matches a searched symbol or value.

How to Return All Matching Rows in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A user wants to extract all matching records rather than return only the first match.
Observed behavior
The desired result is a dynamic list of all rows whose search column matches the entered value.
Before you start

Check that your Excel version supports the FILTER function, such as Microsoft 365 or Excel 2021. Identify the column to search, the columns to return, and an empty area for the results.

Solution 1Recommended

Use FILTER to return every matching row

Use FILTER when you want matching rows to update dynamically as the search value or source data changes.

1
Enter the search value

Type the symbol or value to find in cell M2. In this example, column A contains the values to search and columns B through I contain the data to return.

2
Enter the formula

Select an empty cell such as M4 and enter =FILTER(B4:I21,A4:A21=M2,"No matches"). Press Enter to display all matching rows.

3
Add headers if needed

Copy the source headers into the row above the formula. FILTER returns the data rows specified in the formula, not their headers.

4
Use table references if applicable

For the named table in this example, enter =FILTER(Combined_tblFinancials[[itemCode]:[itemDescTr]],Combined_tblFinancials[Symbol]=A1,"No matches") in an empty cell. Replace A1 with the cell containing your search value if necessary.

Use FILTER to return every matching row
Leave room for the results: FILTER spills results into adjacent cells. Clear any cells blocking the output area if Excel displays a #SPILL! error. The search range and returned range must cover the same number of rows.
Work with matching rows in WPS

Find all matching rows in WPS Spreadsheet

Use the filter controls in WPS Spreadsheet to display all rows matching a symbol or value without writing a formula.

  1. 1. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
  2. 2. Enable filtering: Select a cell in your data range, then choose Data > Filter. Make sure the range includes its header row.
  3. 3. Select the search value: Click the filter arrow in the symbol or value column, select the value you want, and apply the filter.
  4. 4. Review matching rows: Review the visible rows. To show all records again, use the column filter menu to clear the filter.
Display every matching row using column filtersOpen and edit Microsoft Excel XLSX and XLS filesWork with a familiar spreadsheet interfaceUse WPS Office for free
microsoft office alternative - wps office

Frequently Asked Questions

Which Excel versions support FILTER?

FILTER is available in Microsoft 365 and Excel 2021 and later. If your version does not support it, use Data > Filter to display matching rows instead.

What happens when no rows match?

The third FILTER argument controls the no-match result. For example, =FILTER(B4:I21,A4:A21=M2,"No matches") displays “No matches” when the search finds nothing.

Why does the formula show a #SPILL! error?

The result needs empty cells below and beside the formula. Clear the blocked output area, or move the formula to an area with enough space.

Will the results update when I change the search value?

Yes. When the formula references a search cell such as M2, changing that cell recalculates the matching results.