How to Filter a 2D Excel Array When an Adjacent Row Has Data
Question details
The user needs to filter a specific Excel column based on the presence of data in the corresponding rows of an adjacent 2D array.
- Product
- Microsoft Excel (Microsoft 365)
- Device & OS
- not provided
- Scenario
- Filtering data dynamically based on the contents of an adjacent multi-column range.
- Observed behavior
- The user wants a formula to output only the column entries where the corresponding 2D array row is not entirely blank.
Ensure you are using a spreadsheet software version that supports dynamic array functions like FILTER, BYROW, and LAMBDA, such as Microsoft 365 or the latest WPS Office update.
Use the FILTER, BYROW, and LAMBDA Functions
Combine dynamic array functions to check each row for data and filter the target column accordingly.
This method leverages the BYROW function to evaluate each row in the 2D array using a custom LAMBDA formula. The COUNTA function determines if a row has any data, and FILTER extracts the corresponding values from the target column.
Click on the cell where you want the filtered results to begin spilling.
Type the formula =FILTER(B9:B13, BYROW(C9:F13, LAMBDA(r, COUNTA(r)<>0)), ""), adjusting B9:B13 to your target column and C9:F13 to your 2D array.
Press Enter to execute the formula. The filtered results will automatically populate the cells below.
Filter Complex Arrays Effortlessly in WPS Spreadsheet
WPS Spreadsheet fully supports advanced array functions like FILTER, making it incredibly easy to manage and analyze your data sets. You can achieve exactly the same results seamlessly and for free.
- 1. Open your workbook: Launch WPS Office and open your spreadsheet document containing the 2D array.
- 2. Input the formula: Select a blank cell and input the exact =FILTER(B9:B13, BYROW(C9:F13, LAMBDA(r, COUNTA(r)<>0)), "") formula.
- 3. Get your results: Press Enter. WPS Spreadsheet will calculate and spill the filtered results perfectly just like Microsoft 365.
Frequently Asked Questions
Can I use this formula in older versions of Excel?
No, functions like BYROW, LAMBDA, and FILTER are dynamic array functions introduced in Microsoft 365 and Excel 2021. Older versions do not support them natively.
What if I want to filter based on a specific value rather than just any data?
You can modify the LAMBDA function condition. For example, to check if a row contains the number 5, you could use =FILTER(B9:B13, BYROW(C9:F13, LAMBDA(r, COUNTIF(r, 5)>0)), "").
Why am I getting a #SPILL! error?
A #SPILL! error occurs when the formula's output range is blocked by existing data. Clear the cells below the formula so the array can expand.
Can I filter multiple columns instead of just one?
Yes. Simply change the first argument of the FILTER function from a single column (like B9:B13) to a wider range (like A9:B13), and the formula will return all specified columns for the matching rows.
