Fix Excel CHOOSE, BYROW, and BYCOL Returning Repeated Values or Errors
Question details
The user needs to resolve repeated values or #N/A errors that occur when using dynamic array formulas like CHOOSE, BYROW, and BYCOL with differently dimensioned results.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Building complex dynamic array formulas that output arrays of varying sizes or dimensions.
- Observed behavior
- The functions return repeated scalar values or #N/A errors instead of the expected nested or jagged array, because Excel resizes incompatible dimensions.
Ensure you are using a version of Excel that supports dynamic array formulas (such as Microsoft 365 or Excel 2021) and review the exact dimensions of your source data arrays.
Reshape Jagged Arrays using REDUCE and HSTACK
REDUCE combined with HSTACK can iterate through array results of varying sizes and horizontally stack them into a single, clean rectangular array.
Excel does not natively support jagged arrays (arrays of arrays). When the selector is an array, Excel resizes the return values to compatible dimensions, which causes #N/A errors or repeated scalars. By combining the REDUCE and HSTACK functions, you can force Excel to stack arrays of varying sizes into a single rectangular grid.
Use the LET function to define your array selector and the various possible array outcomes you want to evaluate.
Write a REDUCE function that iterates over the sequence of your arrays. Within its LAMBDA, use HSTACK to append each array to an accumulator variable horizontally.
Wrap your final output formula in IFNA() or IFERROR() to replace any mismatched dimension #N/A errors with blank spaces ("").
Use MAP Instead of BYROW or BYCOL for Element Processing
Since BYROW and BYCOL cannot return arrays of arrays, MAP is a better alternative for processing elements individually.
Construct a Rectangular Array using MAKEARRAY
Generate a uniform grid using MAKEARRAY and retrieve the corresponding values using INDEX to bypass jagged array limitations.
A Free, Lightweight Alternative for Advanced Spreadsheets
Handling complex array formulas requires robust software. WPS Office is a powerful, free alternative to Microsoft Office that offers seamless format compatibility, a familiar interface, and excellent performance for complex spreadsheet tasks without the heavy subscription cost.
- 1. Download and Install: Get WPS Office for free from the official website and install it on your device.
- 2. Open Your Spreadsheets: Double-click your existing .xlsx files to open them directly in WPS Spreadsheet.
- 3. Work with Formulas: Continue using your advanced array formulas with complete compatibility.
Frequently Asked Questions
Why does CHOOSE repeat scalar values in array formulas?
When you use an array as the selector argument in CHOOSE, the application attempts to prevent a jagged array. It resizes scalar results by repeating them to match the dimensions of the array selector.
Can BYROW or BYCOL return an array of arrays?
No, spreadsheet software does not natively support nested arrays or arrays of arrays. BYROW and BYCOL require their LAMBDA function to return a single result per row or column.
How do I remove #N/A errors when generating jagged arrays?
When merging arrays of different dimensions, the empty spaces are automatically filled with #N/A errors. You can remove these by wrapping your final array formula in IFNA() or IFERROR() to replace the errors with a blank string ("").
