1) Cheat Sheet

// REDUCE([initial_value], array, LAMBDA(acc, current, calculation))
=REDUCE(0, A2:A100, LAMBDA(a,v, a+v))

// SCAN([initial], array, LAMBDA(acc, current, calculation))
=SCAN(0, A2:A10, LAMBDA(a,v, a+v))      // Running total

// MAP(array1, [array2], ..., LAMBDA(v1, v2, ..., calculation))
=MAP(Qty, Price, LAMBDA(q,p, q*p))      // Element-wise multiplication

// MAKEARRAY(rows, cols, LAMBDA(r,c, calculation))
=MAKEARRAY(6,7, LAMBDA(r,c, DATE(2025,8,0)+r*7+c))

TIP Using Tables and structured references together makes formulas safer when columns are moved.

2) REDUCE: Seven Accumulation and Aggregation Recipes

// ① Conditional sum (without a PivotTable)
=REDUCE(0, SalesTbl[Amount],
  LAMBDA(a,v, a + IF(INDEX(SalesTbl[Region], XMATCH(v, SalesTbl[Amount]))="Seoul", v, 0)))

/* ② Concatenate text (comma-separated) */
=LET(arr, FILTER(Items, Qty>=10),
  REDUCE("", arr, LAMBDA(a,v, IF(a="", v, a&", "&v))))

// ③ Cumulative VSTACK (stack multiple tables vertically at once)
=REDUCE(TAKE(T1,1), {T1,T2,T3}, LAMBDA(a,t, VSTACK(a, DROP(t,1))))

// ④ Unique items and counts (dictionary-like)
=LET(u, UNIQUE(Items),
  HSTACK(u, MAP(u, LAMBDA(x, COUNTIF(Items, x)))))

/* ⑤ Cumulative daily maximum (number of record-setting points) */
=LET(s, SalesTbl[Amount], r, SalesTbl[Date],
  BYROW(SCAN(-1,s,LAMBDA(a,v, MAX(a,v))), LAMBDA(x, 1)))

// ⑥ Accumulate scores with multiple conditions
=REDUCE(0, ScoreTbl[Rule],
  LAMBDA(a,row, a + IF(XLOOKUP([@Key], Rule[Key], Rule[Pts],0)>0, XLOOKUP([@Key], Rule[Key], Rule[Pts]), 0)))

// ⑦ Average excluding N/A (direct accumulation)
=LET(s, FILTER(A2:A, A2:A<>""), REDUCE(0, s, LAMBDA(a,v, a+v))/ROWS(s))

3) SCAN: Seven Running-Value and Step-by-Step Result Recipes

// ① Running total
=SCAN(0, SalesTbl[Amount], LAMBDA(a,v, a+v))

// ② Running maximum/minimum
=SCAN(-1E99, SalesTbl[Amount], LAMBDA(a,v, MAX(a,v)))
=SCAN(1E99,  SalesTbl[Amount], LAMBDA(a,v, MIN(a,v)))

// ③ Moving average (n=7)
=LET(n,7, SCAN(0, A2:A100, LAMBDA(a,v, (a*(n-1)+v)/n)))

// ④ Target-reaching point (first TRUE)
=XMATCH(TRUE, SCAN(0, A2:A, LAMBDA(a,v, a+v))>=100000)

// ⑤ Running unique count
=SCAN(0, A2:A, LAMBDA(a,v, ROWS(UNIQUE(TAKE(A2:A, ROWS(A2:v))))))

// ⑥ Running inventory balance (inbound/outbound +/−)
=SCAN(0, Movements[Qty], LAMBDA(a,v, a+v))

// ⑦ Cumulative percentage (Pareto)
=LET(t,SUM(A2:A), SCAN(0, SORT(A2:A,-1), LAMBDA(a,v, (a+v)/t)))

4) MAP: Seven Element-Wise Transformation Recipes

// ① Unit price × quantity = amount
=MAP(Orders[Qty], Orders[Price], LAMBDA(q,p, q*p))

// ② Conditional labeling
=MAP(SalesTbl[Amount], LAMBDA(v, IF(v>=100000,"A","B")))

// ③ Transform multiple arrays at once (price including VAT)
=MAP(Net, VAT, LAMBDA(n,vat, n*(1+vat)))

// ④ Normalize text (spaces and letter case)
=MAP(A2:A, LAMBDA(t, PROPER(TRIM(SUBSTITUTE(t,CHAR(160)," ")))))

/* ⑤ Extract the domain from a URL */
=MAP(URLs, LAMBDA(u, TEXTAFTER(TEXTBEFORE(u,"/",3),"//")))

// ⑥ Apply price formatting
=MAP(Prices, LAMBDA(p, TEXT(p,"#,##0")))

// ⑦ Error-friendly display
=MAP(Result, LAMBDA(x, IFERROR(x,"-")))

5) MAKEARRAY: Seven Calculated Array Creation Recipes

// ① Monthly calendar (rows=weeks, columns=days)
=MAKEARRAY(6,7, LAMBDA(r,c, DATE(2025,8,1) - WEEKDAY(DATE(2025,8,1),2) + (r-1)*7 + c))

// ② Multiplication table
=MAKEARRAY(9,9, LAMBDA(r,c, r*c))

// ③ Coordinate row (display column indexes)
=MAKEARRAY(1, COLUMNS(A2:Z2), LAMBDA(r,c, c))

// ④ Random number matrix (0–1)
=MAKEARRAY(10,5, LAMBDA(r,c, RAND()))

// ⑤ Conditional mask (1 for values at or above the threshold)
=MAKEARRAY(ROWS(A2:A10),1, LAMBDA(r,c, --(INDEX(A2:A10,r)>=B1)))

// ⑥ Column name replication matrix
=MAKEARRAY(ROWS(A2:A6), COLUMNS(A1:F1), LAMBDA(r,c, INDEX(A1:F1,c)))

// ⑦ Custom index (every Monday)
=MAKEARRAY(10,1, LAMBDA(r,c, TEXT(TODAY()+7*(r-1),"yyyy-mm-dd")))

6) Error and Performance Checklist

• #SPILL! — Clear values, merged cells, and shapes from the spill range.
• Use structured references when working with Tables, such as SalesTbl[Amount].
• For large datasets, store unnecessary intermediate spills in LET variables and minimize calculations inside MAP/SCAN.
• For errors, use IFERROR to provide user-friendly messages.

Next, combine these functions with VSTACK/HSTACK or a searchable drop-down list to complete your automation pipeline.