📎 This article includes 1 downloadable practice file ↓
In this article
Your custom functions work on one value. These helpers run them over whole ranges in a single spilled formula.
| Helper | Runs the LAMBDA on | Example |
|---|---|---|
| MAP | each cell (or matching cells of several ranges) | =MAP(E2:E6, CLEANNAME) |
| BYROW | each row | =BYROW(B2:D4, LAMBDA(r, SUM(r))) |
| BYCOL | each column | =BYCOL(B2:D4, LAMBDA(c, MAX(c))) |
| MAKEARRAY | each row × column position | =MAKEARRAY(3,3,LAMBDA(r,c,r*c)) |
Notice =MAP(E2:E6, CLEANNAME): when the function takes one argument, you can pass the name directly (“eta” style) without writing LAMBDA(t, CLEANNAME(t)).
MAP with two ranges
=MAP(B2:B6, C2:C6, LAMBDA(amt, rate, GSTINCL(amt, rate)))Each amount is paired with its own rate. That’s a whole GST column in one formula.
=LET(t,BYROW(B2:D4,LAMBDA(r,SUM(r))),INDEX(A2:A4,MATCH(MAX(t),t,0))).Common mistakes
- BYROW’s LAMBDA returning several values gives #CALC!. Each row must return one value.
- Ranges of different sizes in MAP give #VALUE!.
Practice
Download the workbook below. The Grid sheet has quarterly targets for three regions. 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.
📎 Practice files for this article
- 📗Lesson 6 practice workbookRegional target grid + 6 array tasks.⬇ XLSX · 14 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.