Function Problems

How to Use Excel Formula to Return All Matching Rows and Ignore Blanks

Nimra Malik Oct 10, 2026 872 views

Question details

The user needs to extract all matching records from a dataset while ignoring blank cells, replacing a standard lookup formula that only returns the first match.

How to Use Excel Formula to Return All Matching Rows and Ignore Blanks

Product: Excel
Device & OS: not provided
Scenario: Filtering a dataset with missing data entries to spill every valid item name and quantity into consecutive rows.
Observed behavior: Lookup formulas typically return just the first match. The goal state is to output every matching, non-blank row dynamically.

Before you start

Ensure your spreadsheet software supports dynamic array formulas, as the FILTER function is required to replace traditional multi-cell array formulas.

Solution 1Recommended

Use the FILTER Function to Extract Non-Blank Rows

The FILTER function is the most efficient way to replace INDEX MATCH for retrieving multiple matches and spilling them into adjacent rows.

Traditional lookup functions like VLOOKUP and INDEX MATCH are designed to return only the first matching value they find. To extract all matching records and skip blank cells, you should use the modern FILTER function.

The FILTER function allows you to specify a criteria (like 'not equal to blank') and automatically spills the resulting dataset into adjacent rows and columns.

Select the destination cell

Click on an empty cell (e.g., D2) where you want the first result of your extracted data to appear.

Enter the FILTER formula

Type the formula =FILTER(A2:B19, A2:A19<>""). Adjust A2:B19 to cover your entire source data range, and A2:A19 to represent the column you want to check for blanks.

Execute the formula

Press Enter to execute the formula. The spreadsheet will dynamically spill all matching parts and quantities into successive rows below your selected cell.

Use the FILTER Function to Extract Non-Blank Rows

Dynamic Spilling: Make sure there is enough empty space below and to the right of your formula cell; otherwise, you will encounter a #SPILL! error.

Advanced Spreadsheet Formulas

Extract and Filter Data Dynamically in WPS Spreadsheet

WPS Office provides robust support for modern dynamic array functions, enabling you to extract all matching rows and ignore blanks without complex workarounds.

1. Open Your Data: Launch WPS Spreadsheet and open the document containing your dataset.
2. Apply the Formula: In an empty column, input =FILTER(A2:B19, A2:A19<>"") to target your data range and filter out blanks.
3. View Spilled Results: Hit Enter to immediately view all non-blank matches arranged neatly in consecutive rows.

Fully compatible with Microsoft Excel .xlsx format and modern array formulas.Easily execute complex data extraction tasks like FILTER and XLOOKUP.Free, lightweight, and features a familiar user interface for immediate productivity.

microsoft office alternative - wps office

Frequently Asked Questions

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

This error occurs when the cells where the formula needs to place the results are not completely empty. Clear any text, spaces, or formatting in the cells below and to the right of your formula to fix this issue.

Can I filter based on multiple conditions instead of just ignoring blanks?

Yes, you can multiply conditions in the FILTER function criteria. For example, =FILTER(A2:B19, (A2:A19<>"")*(B2:B19>10)) ignores blanks in column A and only returns rows where column B is greater than 10.

Does INDEX MATCH work for returning multiple matches?

Standard INDEX MATCH only returns the first matching value. To return multiple matches in older spreadsheet versions without the FILTER function, you must use highly complex array formulas combining INDEX, SMALL, IF, and ROW functions.

Excel & Data Problems