Excel LAMBDA Lesson 6: MAP, BYROW, BYCOL and MAKEARRAY

Excel LAMBDA Lesson 6: MAP, BYROW, BYCOL and MAKEARRAY featured illustration

πŸ“Ž This article includes 1 downloadable practice file ↓

⏱ 2 min read

πŸ“˜ Excel LAMBDA Library Course Β· Lesson 6 of 8

In this article
  1. MAP with two ranges
  2. Common mistakes
  3. Practice
  4. Continue learning

Your custom functions work on one value. These helpers run them over whole ranges in a single spilled formula.

Advertisement
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.

Advertisement
πŸ’‘ BYROW is perfect for “best region” questions: =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

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.

✨ Ask AI about this article

Stuck on a step? Ask a question and the AI answers using this article.

Free Β· AI can be wrong