ExcelFlux

SCAN function

Scans an array by applying a LAMBDA to each value and returns an array that has each intermediate value.

Logical · Excel 2024+ · Guided Excel example

Where can you use it?

Teams building reports and business rules. Adapt the example’s decision rule when you need to test a condition or display different results based on it.

Scenario in the workbook

Return a running total at every step. The final item is the overall total.

A worked example from the workbook

RowABC
6ValuePositionReading
7114
8429
97314
Record 1
=INDEX(SCAN(0,$A$7:$A$9,LAMBDA(acc,val,acc+val)),B7)

Illustrative output1

See the inputs and instructions for all 3 examples

Worked examples

Return a running total at every step. The final item is the overall total.

Column F provides expressions as text. Satisfy the stated requirements, then paste into an empty Excel cell and compare with column G. The result was not verified in desktop Excel here.

Row A B C
6 Value Position Reading
7 1 1 4
8 4 2 9
9 7 3 14
Trial Expression Sample output
Record 1 =INDEX(SCAN(0,$A$7:$A$9,LAMBDA(acc,val,acc+val)),B7) 1
Record 2 =INDEX(SCAN(0,$A$7:$A$9,LAMBDA(acc,val,acc+val)),B8) 5
Record 3 =INDEX(SCAN(0,$A$7:$A$9,LAMBDA(acc,val,acc+val)),B9) 12
Microsoft Excel reference