Practice free

SCAN function

SCAN walks through an array and returns the running result at each step, like REDUCE but keeping every intermediate value. It suits running totals, and the output has the same shape as the input array.

Example

Read it as a sentence: start a running total at 0, add each of 1, 2, 3 and 4 to it, and join the running totals with commas, which gives 1,3,6,10.

Practice this in the app

Syntax

SCAN([initial_value], array, lambda)

Arguments in [square brackets] are optional.

initial_value (optional)
Starting value of the accumulator.
array (required)
The array to loop over.
lambda (required)
A LAMBDA(accumulator, value) returning the new accumulator.

Returns: An array of every intermediate accumulator value.

One SCAN formula can fill more than one cell. When the result has several values, they spill into the cells below or to the right of the formula.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=SCAN(0,{1,2,3},LAMBDA(a,v,a+v)) 1 Running total
=SCAN("",{"a","b","c"},LAMBDA(a,v,a&v)) a Running text
=SCAN(0,{1,2;3,4},LAMBDA(a,v,a+v)) 1 2D keeps shape
=SCAN(,{1,2},LAMBDA(a,v,a+v)) 1 Omitted initial value
=SCAN(0,{1,2},LAMBDA(a,a)) #VALUE! One-parameter lambda
=SCAN(0,{1,2},LAMBDA(a,v,{1,2})) #CALC! Lambda returns array

Watch out for

  • Output has the same shape as the input array.
  • Without an initial value the first item seeds the accumulator.

Where it works

Excel
SCAN arrived in Excel 2024. Older versions do not have it. Dynamic-array LAMBDA family; in 365 and Excel for the web, and Excel 2024. Check older perpetual versions.
Google Sheets
Same name, same behaviour for everyday use.

Part of Logical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .