Excel LAMBDA Lesson 5: REDUCE and SCAN (Running Balances, Multi-Replace)

📎 This article includes 1 downloadable practice file ↓

📘 Excel LAMBDA Library Course · Lesson 5 of 8

REDUCE and SCAN walk through a list one item at a time, carrying a running value (the accumulator).

=REDUCE(start, list, LAMBDA(acc, item, new_acc))   → only the final value
=SCAN(start, list, LAMBDA(acc, item, new_acc))     → every step

Running bank balance

=SCAN(1000, {-200, 500, -300}, LAMBDA(bal, txn, bal + txn))   → 800, 1300, 1000
=MIN(SCAN(...))                                                → 800, the lowest point

That lowest point is what a bank looks at for minimum-balance charges, and it’s one formula.

MULTIREPLACE: many SUBSTITUTEs in one

MULTIREPLACE =LAMBDA(t, old, new,
   REDUCE(t, SEQUENCE(COLUMNS(old)),
     LAMBDA(a, i, SUBSTITUTE(a, INDEX(old, i), INDEX(new, i)))))

=MULTIREPLACE("ABC Pvt Ltd", {"Pvt","Ltd"}, {"Private","Limited"})   → ABC Private Limited

Point old and new at two columns of a table and your replacement list becomes editable without touching the formula. Turn the columns into rows with TRANSPOSE, or use ROWS instead of COLUMNS.

Common mistakes

• Argument order inside the LAMBDA: always (accumulator, item).
• Returning an array from REDUCE needs care; for lists of results use SCAN or MAP.

Practice

Download the workbook below. The tasks call each function inline, like =LAMBDA(x,x*2)(A2), so the file works on any Microsoft 365 PC; in your own files, save them by name in Name Manager. The Check column turns green when you’re right.