Google Sheets' Lambda Function

Question

Can someone help me figure out how Google Sheets' inbuilt Lambda function works? There is no official documentation for it and the only information available is as in the below snapshot.

When using the lambda function as per the Help Box, Google Sheets is showing error as below.

Answer 1

what is LAMBDA

LAMBDA function allows users to define a new function with optional parameters and a formula that utilizes these parameters.

when a LAMBDA function is defined, google sheets creates a mapping of the parameter names to their positions. this mapping is then used to substitute the parameters with the actual values when the function is invoked.

function is called by appending arguments to it. google sheets matches the provided arguments to the parameter positions, replacing the parameter names in the formula with the corresponding argument values.

google sheets then evaluates the formula within the LAMBDA function's context, that includes the parameter values and any named ranges or constants that might be referenced.

the scope of a LAMBDA function's parameters is limited to the function itself, ensuring that parameter names do not conflict with names outside the function. LAMBDA functions can call themselves recursively.

LAMBDA function in google sheets (announced 24 Aug 2022) was introduced with a new feature called "Named functions", that allows you to create powerful, custom and reusable functions with very complex logic and calculations directly within a spreadsheet. these "Named functions" can be then used as any other formula, which can enhance overall maintainability of your spreadsheet by simplifying your formulae beyond your wildest imagination.

unfortunately, "Named functions" are saved per spreadsheet and not per account, so with every new spreadsheet, all custom functions need to be manually reimported, and the 2nd major drawback is, that due to poor interface, it is very hard to retro-engineer complex custom function created by someone else in order to fix it or extend its capabilities.

---

how to use LAMBDA

luckily, LAMBDA function (although it was presented within a bundle of other helper functions - MAP, REDUCE, BYROW, BYCOL, SCAN, MAKEARRAY) can be also used as standalone formula.

syntax is a bit unusual compared to the rest of the formulae, where range is placed in brackets () after closing the LAMBDA (this applies only if LAMBDA is used as a standalone function without lambda helper functions):

=LAMBDA(x; x)(A1)

first (and any following) argument, excluding the last argument inside LAMBDA, serves as placeholder. this placeholder can be any basic (non-exotic - ♥, ♫, ☼) symbol (a, B, ц, Д, Σ, 커, 帝, 国) or word (arg1, range, xxx, ФЧщ, 뉴럭이) from any major alphabet unless it is not any kind of (possibly) valid range (5:6, zzz1, AB6) or numerical values (1, 99, -2, 0.5). allowed are also underscores and suffixed numbers (rty_123, ted689). placeholders are not case-sensitive.

last argument inside LAMBDA serves as execution of placeholder. it can be combined with a variety of other formulae, values, strings or logic operators arranged in a way to get you the desired output

the part after closed LAMBDA always needs to hold valid range or construct resulting in valid range. supported are INDEX, OFFSET, INDIRECT, IMPORTRANGE etc.

---

as mentioned, LAMBDA can handle multiple placeholders, but each needs to be properly referenced after closed LAMBDA (x=A1, y=B1, z=C1):

=LAMBDA(x; y; x)(A1; B1)
=LAMBDA(x; y; z; z & y & x)(A1; B1; C1)

simple LAMBDA also does not need to be wrapped into INDEX, ARRAYFORMULA, etc. to be able to process arrays:

=LAMBDA(y; Y)(A1:A5)

but with added complexity, a wrapper for array execution is required:

=ARRAYFORMULA(LAMBDA(x; Y; y & x)(A1:A2; B1:B2))

one of the advantages of LAMBDA is the ability to freeze volatile functions. for example, print timestamp upon A1 edit:

=LAMBDA(x; x)(IFERROR(A1/0)+NOW()) 

another LAMBDA advantage is to minimize calls for a range in order to significantly shorten the formula for clarity. for example, the first formula (uses two ranges) can be substituted for the second formula (that uses only one range):

=INDEX(LAMBDA(a; b; a & b)(A1:A5; B1:B5))
=INDEX(LAMBDA(a; a&OFFSET(a;;1))(A1:A5))

even nesting of multiple LAMBDA functions is allowed:

=LAMBDA(x; LAMBDA(zzz; zzz)(x))(A1:A5)

overall LAMBDA function can be a very efficient tool, and when used as a standalone function it is not a subject of any limitations.

back in a day, LAMBDA function was favored by masses, however, since 1 Feb 2023 the spotlight was stolen by LET function which acts very similarly to LAMBDA but without less awkwardness. with LET function, we can define placeholders in a more natural way:

=INDEX(LET(aa; A2:A10; cc; OFFSET(aa;;2); xx; "Yuki"; IF(aa=xx; cc; )))

so nowadays is LAMBDA used mostly with lambda helpers (MAP, REDUCE, BYROW, BYCOL, SCAN, MAKEARRAY) to convert single-celled formulae into array formula variant surpassing limits of good-old ARRAYFORMULA.

Answer 2

Google officially released the lambda function and its helper functions, but not all users were supported at the time you posted your question, so the error message indicates that it is an unknown function.

See support.google for the LAMBDA function.