Function Problems

How to Return Only Matching Excel Rows Without Blanks Using FILTER

Nimra Malik Oct 22, 2026 868 views

Question details

Extract specific data rows matching multiple conditions without returning blank rows for unmet criteria.

How to Return Only Matching Rows in Excel Without Blank Cells

Product: Excel
Device & OS: not provided
Scenario: Pulling data from column I when column B equals 'Registered' or 'In Progress', and column J equals 'Yes'.
Observed behavior: Standard IF formulas evaluate row by row and return empty cells for false conditions, leaving undesirable blank rows scattered throughout the extracted dataset.

Before you start

Verify your current spreadsheet software version, as the modern FILTER function is exclusively available in Microsoft 365, Excel 2021, and modern suites like WPS Office Spreadsheet.

Solution 1Recommended

Use the Dynamic FILTER Function

The most efficient way to extract data without blanks using modern spreadsheet software.

The FILTER function natively supports arrays and completely skips non-matching rows. It allows you to combine OR logic (using the '+' symbol) and AND logic (using the '*' symbol) in a single formula.

Select the destination cell

Click on the top-left empty cell in the worksheet where you want your extracted list to begin.

Enter the FILTER formula

Type the formula: =FILTER(Data!I:I, ((Data!B:B="Registered")+(Data!B:B="In Progress"))*(Data!J:J="Yes"), "No Match")

Apply the dynamic array

Press Enter. The results will automatically spill down the column, showing only the matching values from column I without any blank gaps.

Formula Breakdown: The '+' adds the two column B conditions together (meaning 'Registered' OR 'In Progress'), and the '*' ensures that column J must also be 'Yes'.

Solution 2

Use an INDEX and SMALL Array Formula

A backward-compatible solution for Excel 2019 and older versions that do not support dynamic arrays.

Seamless Spreadsheet Data Filtering

Filter Complex Data Instantly in WPS Office

WPS Spreadsheet fully supports modern dynamic array functions like FILTER, allowing you to easily extract data matching multiple conditions without leaving annoying blank rows.

1. Install WPS Office: Download and open WPS Office Spreadsheet on your computer.
2. Open your workbook: Import your existing Excel workbook containing the raw data you need to filter.
3. Select destination: Select an empty column or a new sheet where you want the filtered data to appear.
4. Apply the FILTER formula: Type the formula =FILTER(Data!I:I, ((Data!B:B="Registered")+(Data!B:B="In Progress"))*(Data!J:J="Yes"), "No Match") and press Enter.
5. View clean results: Instantly view the extracted rows perfectly stacked without any empty cell spaces.

Fully compatible with Microsoft Excel formulas and .xlsx formats.Supports dynamic arrays and the FILTER function out of the box.A free, lightweight, and highly efficient alternative to heavy office suites.

Frequently Asked Questions

Can I use the FILTER function across different worksheets?

Yes, you can easily reference data from another worksheet by prefixing the column references with the sheet name and an exclamation mark, such as Data!I:I.

How do AND and OR logic work in array formula conditions?

In array formulas and dynamic functions like FILTER, the asterisk (*) acts as an AND operator, requiring both conditions to be true. The plus sign (+) acts as an OR operator, meaning the row is returned if either condition is met.

Why does my array formula show the formula text instead of the result?

The cell might be formatted as Text. Change the cell format to General in the Home ribbon, then double-click the cell and press Enter (or Ctrl+Shift+Enter for older Excel versions) to evaluate it properly.

What should I do if my FILTER function returns a #SPILL! error?

A #SPILL! error occurs when the dynamic array formula does not have enough empty adjacent cells to display the results. Simply clear any text or data in the cells directly below your formula to allow the data to populate.

Excel & Data Problems