Scans an array by applying a LAMBDA function to each value and returns an array that has each intermediate value
Master the fundamentals of Excel SCAN function
The SCAN function applies a LAMBDA function to each element of an array, returning an array of all intermediate accumulated values. It is useful for tracking the progression of calculations.
SCAN returns all intermediate values, unlike REDUCE which returns only the final result
SCAN requires a LAMBDA function that takes accumulator and current value parameters
Use SCAN to track how calculations progress through an array
Function-specific parameters
Function-specific return type
SCAN returns all intermediate values, unlike REDUCE which returns only the final result
SCAN requires a LAMBDA function that takes accumulator and current value parameters
Use SCAN to track how calculations progress through an array
Exact matching required
Returns numeric position
Handles missing text gracefully
=SCAN(initial_value, array, lambda)Optional. The initial value for the accumulator
The array to scan
A LAMBDA function that takes accumulator and current value, returns new accumulator
An array with all intermediate accumulated values
Description: SCAN([initial_value], array, lambda)
Basic SCAN function
Scans A1:A3 and returns running sum at each step
VBA implementation for SCAN.
Sub SCANExample()
Dim result As Variant
' Note: SCAN is not available in VBA, use alternative methods
' This is a conceptual example
result = Application.WorksheetFunction.Scan(0, Range("A1:A3"), "LAMBDA(acc, val, acc+val)")
MsgBox "SCAN result: " & result
End SubRunning calculations
Progressive aggregation
Cumulative operations
Data tracking
Intermediate results
SCAN not working
Solution: Ensure the LAMBDA function syntax is correct and takes accumulator and value parameters
Unexpected array size
Solution: Check that the input array and LAMBDA logic are appropriate for your use case