Function Problems

How to Filter a Two-Dimensional Array in Excel

Algirdas Jasaitis Oct 12, 2026 871 views

Question details

The user wants to use the FILTER function to extract matching values from a two-dimensional range and output them as a compact result (e.g., a single column or row) instead of preserving their original positions.

How to Filter a Two-Dimensional Array in Excel

Product: Excel
Device & OS: not provided
Scenario: Extracting specific matching values from a 2D data grid and condensing them into a single column or row output.
Observed behavior: Standard filtering on a 2D array might preserve original empty positions or result in errors. The goal is to output a clean, compact array containing only the matching values.

Before you start

Ensure you are using a version of Excel (like Microsoft 365) or WPS Spreadsheet that supports modern dynamic array functions such as FILTER, LET, TOROW, and TOCOL.

Solution 1Recommended

Flatten the Array Before Filtering Using LET and TOROW

Convert the 2D array into a 1D array first, then apply the FILTER function to return a compact list without preserving original empty grid spaces.

By nesting the TOROW (or TOCOL) function inside a LET statement, you can flatten your 2D data grid into a single row or column. Once flattened, the FILTER function can easily evaluate the criteria and output a continuous array of matching values.

Select the target cell

Click on the cell where you want the filtered dynamic array to begin spilling its results.

Enter the LET and TOROW formula

Type the formula to flatten and filter the data. For example: =LET(data,TOROW(A1:E5),FILTER(data,(LEFT(data)="a")*((RIGHT(data)="1")+(RIGHT(data)="3"))))

Apply the formula

Press Enter. The results will automatically spill horizontally into a compact single row.

Flatten the Array Before Filtering Using LET and TOROW

Vertical Output: If you prefer the results in a single column instead of a row, replace TOROW with TOCOL in the LET function declaration.

Solution 2

Extract Matches Using TEXTJOIN and TOCOL

Join all matching values into a single text string, bypassing empty non-matching cells, then split them back into a vertical array.

Advanced Data Analysis

Easily Handle Dynamic Arrays in WPS Spreadsheet

WPS Office fully supports dynamic array functions like FILTER, LET, and TOCOL. You can easily process complex 2D data arrays, extract matches, and manipulate ranges without worrying about formula compatibility.

1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the 2D array data.
2. Input the dynamic formula: Select an empty cell and enter your flattening and filtering formula, such as =LET(data, TOCOL(A1:E5), FILTER(data, criteria)).
3. Press Enter to spill: Hit Enter. WPS Spreadsheet will automatically calculate and spill the compact results into the adjacent cells perfectly.

Full compatibility with Microsoft Excel formulas and functionsFree and lightweight spreadsheet softwareNative support for modern dynamic array spillingIntuitive interface for complex data analysis

microsoft office alternative - wps office

Frequently Asked Questions

Can I return the filtered array as a single column instead of a row?

Yes. If your formula uses TOROW to flatten the data, simply replace it with TOCOL inside your LET function. This ensures the output is structured vertically as a single column.

Why does my FILTER formula return a #CALC! error?

The #CALC! error typically occurs if the FILTER function finds no matching values based on your criteria. You can prevent this by adding the optional [if_empty] argument, like FILTER(data, condition, "No Match").

Are these dynamic array functions backward compatible?

Functions like FILTER, TOCOL, and TOROW are only available in modern platforms that support dynamic arrays, such as Microsoft 365, Excel 2021, and the latest versions of WPS Office. Older versions require complex index-match CSE (Ctrl+Shift+Enter) array formulas.

Excel & Data Problems