Skip to content
Google Sheets

SCAN function in Google Sheets

Scans an array and produces intermediate values by application of a LAMBDA function to each value. Returns an array of the intermediate values obtained at each step.

=SCAN(initial_value, array_or_range, LAMBDA)

SCAN syntax and parameters

Three arguments, three required.

  • initial_valueanyRequired

    The initial accumulator value.

  • array_or_rangeanyRequired

    An array or range to be scanned.

  • LAMBDAanyRequired

    A LAMBDA that’s applied to each value in array_or_range for scanning it. Syntax: LAMBDA(name1, name2, formula_expression) Requirements: The LAMBDA must have exactly 2 name arguments along with a formula_expression which uses those names. The name1 resolves to the current value in the accumulator and name2 resolves to the current_value in array_or_range, when applying the LAMBDA. The accumulator is updated in each step to the intermediate value obtained in the previous step.

SCAN examples

Formulas you'll actually reuse.

  1. A running total down the column — every intermediate value:

    =SCAN(0, D2:D200, LAMBDA(acc, x, acc + x))

  2. The record-to-date — the biggest deal seen so far on each row:

    =SCAN(0, D2:D200, LAMBDA(acc, x, MAX(acc, x)))

SCAN in Excel

Same name — your formula ports as-is.

Try SCAN in the playground

Edit the example — nothing to install.

Preloaded with the SCAN formula from Example 1 — change anything and watch it respond.

Loading the editor…

Related functions

More ways Google Sheets gets this done.