Function Problems

Why Excel BYROW Passes a 1x1 Array to LAMBDA and How to Fix It

WPS Content Manager Sep 30, 2026 869 views

Question details

The user is trying to understand why the BYROW function passes a 1x1 array to a nested LAMBDA function instead of a single value when selecting a single-column range, and how to resolve this for legacy functions requiring a scalar.

Why Excel BYROW Passes a 1x1 Array to LAMBDA and How to Fix It

Product: Microsoft Excel
Device & OS: not provided
Scenario: Using the BYROW function on a single-column range to pass row data into a custom LAMBDA function that utilizes other formulas.
Observed behavior: Excel passes the single cell as a 1x1 array rather than a scalar value, which causes errors when the nested LAMBDA function calls a legacy formula expecting a single scalar input.

Before you start

Verify whether the nested formula inside your LAMBDA strictly requires a scalar value, as some newer Excel functions naturally handle arrays without requiring conversion.

Solution 1Recommended

Use the Implicit Intersection Operator (@) to Convert to Scalar

The quickest and most efficient way to convert a 1x1 array into a scalar value inside a LAMBDA function is by using the @ symbol.

Because BYROW is designed to handle rows that might contain multiple columns, it always passes data as an array. The implicit intersection operator (@) forces Excel to extract a single value from that array.

Set up your BYROW function

Begin typing your standard formula, such as =BYROW(SEQUENCE(10), LAMBDA(rw, ...)).

Apply the implicit intersection operator

Inside the LAMBDA function, place the @ symbol directly before your row variable name when passing it to the nested function. For example: myFunc(@rw).

Complete and execute the formula

Ensure all parentheses are closed, resulting in a formula like =BYROW(SEQUENCE(10), LAMBDA(rw, myFunc(@rw))), then press Enter to calculate.

Use the Implicit Intersection Operator (@) to Convert to Scalar

Single-Column Limitation: This approach is strictly for single-column arrays. If your BYROW range spans multiple columns, using @ will only extract the intersection value and may not produce the intended multi-column result.

Solution 2

Extract the Scalar Value Using the INDEX Function

If you prefer not to use the @ operator or need more control, the INDEX function can target and extract the specific scalar value from the 1x1 array.

Master Advanced Functions in WPS

Use WPS Spreadsheet for Powerful Array Calculations

WPS Spreadsheet offers robust, built-in support for complex formulas, dynamic arrays, and advanced data processing. Easily manage logical arrays and perform heavy calculations in an interface you are already familiar with.

1. Open WPS Spreadsheet: Launch WPS Office and open a new or existing spreadsheet document.
2. Select the Target Cell: Click on the cell where you want your formula results to spill or calculate.
3. Input the Array Formula: Type your desired formula utilizing advanced array functions and press Enter to instantly process your data.

Fully compatible with Microsoft Excel formulas and functionsAdvanced support for dynamic arrays and complex spreadsheet logicFree and lightweight alternative for professional data analysisFamiliar UI that requires zero learning curve to migrate

microsoft office alternative - wps office

Frequently Asked Questions

Why is BYROW designed to pass arrays instead of single values?

BYROW is designed to process entire rows of a given array. Because a single row can span across multiple columns (e.g., A1:C1), the function inherently treats the passed parameter as an array to ensure it can handle multi-column data structures without breaking.

Is there an alternative to BYROW that passes scalar values?

Yes, if you are working with a single-column or single-row range and need to pass individual cell values (scalars) to a LAMBDA function, you can use the MAP function instead of BYROW. MAP iterates over individual cells one by one.

Does the BYCOL function behave the same way?

Yes. BYCOL processes data column by column. Even if your selected range only contains a single row, BYCOL will still pass the column parameter to the LAMBDA function as an array. You can use the exact same @ or INDEX workarounds to extract a scalar.

Excel & Data Problems