How to Fix Excel FILTER and BYROW Nested Array Errors
Question details
The user needs to extract one or more customer names per item code within a date range using BYROW and FILTER, but encounters a calculation error when working with spilled arrays.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Returning multiple matching values for a spilled array of criteria using BYROW, LAMBDA, and FILTER.
- Observed behavior
- The formula returns a #CALC! nested-arrays error because the spreadsheet application cannot handle arrays of arrays when FILTER returns multiple matching items inside a BYROW iteration.
Ensure you are using a version of Excel or WPS Office that supports modern dynamic array functions, including BYROW, LAMBDA, FILTER, and spilled ranges.
Use TEXTJOIN to Prevent Nested Arrays in BYROW
Collapse the multiple results from the FILTER function into a single text string per row to avoid the #CALC! nested-array error.
When BYROW iterates over a spilled array (like H5#), it expects the LAMBDA function to return a single value per row. If the nested FILTER function returns multiple customers for a single item code, it creates an array inside an array, which triggers a #CALC! error.
To resolve this, you must flatten the inner array. Wrapping the FILTER function in TEXTJOIN combines multiple returned records into a single cell, satisfying BYROW's single-value requirement.
Start your formula by referencing the spilled range as the array argument in BYROW, for example: =BYROW($H$5#, LAMBDA(row, ...)).
Inside the LAMBDA function, reference the current row variable (e.g., 'row') instead of the spilled range H5# when filtering the item code: (TBL_Cumulative_Bookings[ItemCode]=row).
Write your FILTER function to extract the required columns based on date ranges and item code, then wrap it in UNIQUE() to eliminate any duplicate customer names.
Enclose the UNIQUE(FILTER(...)) logic inside TEXTJOIN("; ", , ...) to combine the resulting array of names into a single comma- or semicolon-separated text string.
Enter the final combined formula: =BYROW($H$5#,LAMBDA(row,TEXTJOIN("; ",,UNIQUE(FILTER(TBL_Cumulative_Bookings[BillToName],(TBL_Cumulative_Bookings[Order Month]>=Oldest_Month)*(TBL_Cumulative_Bookings[Order Month]<=Newest_Month)*(TBL_Cumulative_Bookings[ItemCode]=row))))))
Use WPS Spreadsheet for Advanced Array Formulas
WPS Office provides full support for modern dynamic array functions, including BYROW, LAMBDA, and FILTER, allowing you to build complex data extraction formulas seamlessly without performance lags.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your sales or customer data.
- 2. Select the target cell: Click the top cell where you want the combined spilled array results to appear.
- 3. Enter the dynamic formula: Paste the BYROW and TEXTJOIN combined formula into the formula bar and press Enter.
- 4. View the spilled results: WPS Spreadsheet will automatically calculate the LAMBDA function for each row and spill the unified text results down the column.
Frequently Asked Questions
Why do I get a #CALC! error when using FILTER inside BYROW?
The #CALC! error occurs because the BYROW function requires the LAMBDA formula to return a single result per row. If your FILTER function finds multiple matches, it generates an array of values. Excel and WPS Spreadsheet do not support 'nested arrays' (an array inside an array cell), resulting in the calculation error.
Can I use a spilled array reference like H5# directly inside the FILTER criteria?
When using BYROW, you should pass the spilled array (H5#) as the first argument for the loop. However, inside the LAMBDA function, you must use the declared LAMBDA parameter (e.g., 'row') for your FILTER criteria. Referencing H5# again inside the loop will break the row-by-row iteration.
How do I separate multiple results in the same cell cleanly?
By wrapping your FILTER formula in the TEXTJOIN function, you can specify a delimiter as the first argument, such as a semicolon and space ("; "). This ensures multiple matches are cleanly separated within the same cell text.
