Function Problems

How to Filter Excel Rows That Contain a Matching Value Using Formulas

Tauseeq Magsi Oct 12, 2026 870 views

Question details

The user needs to filter a dataset and extract every row that contains a specific target value in any of its columns.

How to Filter Excel Rows That Contain a Matching Value

Product: Microsoft Excel
Device & OS: not provided
Scenario: Searching for a specific value across a multi-column data range and returning the entire row if a match is found anywhere within that row.
Observed behavior: The desired outcome is to dynamically display all rows where at least one cell in that row matches the specified target search value by combining advanced array formulas.

Before you start

Ensure you are using a modern version of your spreadsheet software that supports dynamic array functions like FILTER and SEQUENCE, such as Microsoft 365, Excel 2021, or WPS Spreadsheet.

Solution 1Recommended

Use the FILTER, MMULT, and SEQUENCE Functions

Combine the FILTER, MMULT, and SEQUENCE functions to evaluate multiple columns simultaneously and return the matching rows.

This dynamic array formula checks every cell in the specified range against your target value. The MMULT and SEQUENCE functions create an array that counts how many times the value appears in each row, while the FILTER function extracts any row where that count is greater than zero.

Identify your data and criteria

Locate your source data range (for example, A1:E5) and the specific cell where your search target is entered (for example, G1).

Select an output cell

Click on an empty cell where you want the filtered results to begin spilling. Ensure there is enough empty space below and to the right so the results are not blocked.

Enter the combination formula

Type the following formula into the selected cell: =FILTER(A1:E5,MMULT(--(A1:E5=G1),SEQUENCE(COLUMNS(A1:E5),1,1,0)))

Execute the formula

Press Enter. The formula will instantly evaluate the data and return all rows from A1:E5 where at least one cell exactly matches the value in G1.

Use the FILTER, MMULT, and SEQUENCE Functions

Adapting for Excel Tables: If you are using formatted Excel Tables, you can replace the A1:E5 range with structured table references (e.g., Table1). The formula will automatically adapt if you add or remove rows from the table.

Advanced Data Filtering in WPS Spreadsheet

Filter Data Seamlessly with WPS Spreadsheet

WPS Spreadsheet fully supports dynamic array functions including FILTER, MMULT, and SEQUENCE. You can seamlessly utilize these advanced combinations to filter multi-column data, extract matching rows, and boost your data analysis efficiency.

1. Open Your Data: Launch WPS Spreadsheet and open your existing Excel workbook (.xlsx).
2. Define the Search Target: Enter your desired matching value into a dedicated criteria cell, such as G1.
3. Apply the Array Formula: Select an empty output cell and paste the combination formula: =FILTER(A1:E5,MMULT(--(A1:E5=G1),SEQUENCE(COLUMNS(A1:E5),1,1,0))).
4. View Dynamic Results: Press Enter to instantly display all rows containing the match, automatically updating if your data changes.

Fully compatible with Microsoft Excel formulas, functions, and .xlsx formats.Built-in support for advanced dynamic array functions like FILTER and SEQUENCE.Lightweight, fast, and completely free to download and use.Intuitive tabbed interface to manage multiple data workbooks easily.

microsoft office alternative - wps office

Frequently Asked Questions

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

The #CALC! error typically occurs when the FILTER function finds no matching records in your dataset. You can prevent this error by adding the optional [if_empty] argument at the end of your formula, for example: =FILTER(A1:E5, MMULT(...), "No matches found").

Can I use this formula to find partial text matches instead of exact matches?

The provided formula only finds exact matches because it uses the equals sign (A1:E5=G1). To find partial text matches, you would need to incorporate the ISNUMBER and SEARCH functions inside the MMULT array logic to identify rows containing a specific text string.

Why does my formula output a #SPILL! error?

A #SPILL! error means the formula is trying to output the filtered rows, but there are non-empty cells blocking the required space. Clear the cells below and to the right of your formula to allow the array results to display properly.

Excel & Data Problems