BYROW function

BYROW applies a LAMBDA to each row of a range and returns a vertical array of the results, enabling row-wise calculations in Excel 365 and Google Sheets.

=BYROW(array, lambda)

Generate a BYROW formula

Describe what you need. The generator will reach for BYROW where BYROW is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How BYROW reads its arguments
arrayrequiredlambdarequiredBYROW →
ArgumentRequiredDescription
arrayRequiredA range or array containing one or more rows; BYROW will feed each entire row to the lambda, and empty rows are still processed.
lambdaRequiredA LAMBDA that receives a single-row array argument; it must return a single value or a consistent sized array, otherwise #VALUE! is raised.

Returns

A vertical array (single column) whose height matches the number of rows in the input array.

Availability

Excel: 365 · Google Sheets: Supported

Worked examples

1. Calculate total sales per order

1001NorthAlice10152024-01-05
1002SouthBob522.52024-01-06
1003EastCarol129.752024-01-07
1004WestDave813.42024-01-08
1005NorthEve207.22024-01-09
=BYROW(A2:F6, LAMBDA(r, INDEX(r,4)*INDEX(r,5)))

Result: [150,112.5,117,107.2,144]

The lambda receives each row as r. INDEX(r,4) picks the Units column, INDEX(r,5) picks Unit Price. Multiplying gives the order total. BYROW stacks each total in a single column, producing the five numbers shown.

2. Flag high-value orders (> $500)

1001NorthAlice10152024-01-05
1002SouthBob522.52024-01-06
1003EastCarol129.752024-01-07
1004WestDave813.42024-01-08
1005NorthEve207.22024-01-09
=BYROW(A2:F6, LAMBDA(r, INDEX(r,4)*INDEX(r,5) > 500))

Result: [FALSE,FALSE,FALSE,FALSE,FALSE]

Each row’s total sales are computed as before, then compared to 500. None of the five orders exceed that threshold, so the lambda returns FALSE for every row, and BYROW returns a column of FALSE values.

3. Create a "Region – Rep" label for each order

1001NorthAlice10152024-01-05
1002SouthBob522.52024-01-06
1003EastCarol129.752024-01-07
1004WestDave813.42024-01-08
1005NorthEve207.22024-01-09
=BYROW(A2:F6, LAMBDA(r, INDEX(r,2)&" - "&INDEX(r,3)))

Result: ["North - Alice","South - Bob","East - Carol","West - Dave","North - Eve"]

The lambda extracts the Region (column 2) and Rep (column 3) from each row, concatenates them with a hyphen, and returns the string. BYROW assembles the five strings into a vertical array.

Common errors

Which BYROW error are you seeing?
BYROW returned an error#VALUE!
Make the lambda return a single value or a one-column array, such as by wrapping the result with INDEX(...,1) or by using TRANSPOSE to force a column shape.
#SPILL!
Clear the cells below the formula or place the formula in a location with enough empty rows to accommodate the output.
#REF!
Update the reference to point to an existing range that contains the sales log rows.
ErrorWhy it happensHow to fix it
#VALUE!The lambda returns an array with more than one column (e.g., using SEQUENCE) while BYROW expects a single-column result per row.Make the lambda return a single value or a one-column array, such as by wrapping the result with INDEX(...,1) or by using TRANSPOSE to force a column shape.
#SPILL!The BYROW result tries to spill into cells that already contain data, preventing the vertical array from expanding.Clear the cells below the formula or place the formula in a location with enough empty rows to accommodate the output.
#REF!The array argument references a range that has been deleted, moved, or is otherwise invalid (e.g., A2:F6 after rows were removed).Update the reference to point to an existing range that contains the sales log rows.

Tips and when to use something else

  • Use BYCOL when you need to apply a LAMBDA to each column instead of each row.
  • Combine BYROW with LET to store intermediate calculations (e.g., pre-compute unit totals) for readability.
  • If you only need a single aggregated value across rows, MAP followed by REDUCE can be more efficient.
  • Remember that BYROW always returns a vertical array; wrap the result in TRANSPOSE if you need a horizontal layout.

Related functions

LAMBDALAMBDA lets you create custom, reusable functions directly in a cell, returning any value or array based on supplied parameters.BYCOLBYCOL applies a LAMBDA to each column of an array, returning a new array that aggregates or transforms column-wise results.MAPMAP applies a custom LAMBDA to each element of a range, returning a new array of the same shape with the calculated results.REDUCEREDUCE iteratively applies a LAMBDA to each element of an array, carrying forward an accumulator that starts with the initial value.LETLET lets you name a single intermediate result inside a formula, then return that result, making complex calculations clearer and easier to read.

Frequently asked questions

Can BYROW be used on a table that includes a header row?
Yes, but the header row will be passed to the lambda like any other row. To skip it, either start the range at the first data row (e.g., A2:F6) or have the lambda test for text values and return a placeholder such as "" for the header.
How does BYROW differ from using an array formula with INDEX and ROW?
BYROW isolates the row logic inside a LAMBDA, making the intent clearer and allowing you to reuse the same lambda elsewhere. Traditional array formulas often require complex INDEX/ROW constructions that are harder to read.
What happens if my lambda returns a different data type for different rows?
BYROW will still spill the results, but mixed types can lead to unexpected behavior in downstream calculations. For consistent results, ensure the lambda always returns the same type (all numbers, all text, or all booleans).
Is BYROW available in older versions of Excel?
No. BYROW is part of the dynamic array functions introduced in Excel 365 and Excel for the web. Users of Excel 2019 or earlier must rely on helper columns or legacy array formulas.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator