LAMBDA function.Create your own function.(Microsoft Excel)


Japanese version.

The LAMBDA function is a new feature that was already added in February 2022 to create custom functions without the use of macros or VBA.
This is useful when the same formula is written in many cells.

How it works

The areas in red are not described depending on the usage.

LAMBDA(Argument name1 to 253,Formula)(Argument1 to 253)
Argument name1 to 253No argumentArgument names.
FormulaRequired argument.
It is possible to specify only the formula without arguments, but
In that case, Name Manager is more effective.
Argument1 to 253No argument



Validate by writing in the cell.

This is for verification purposes only, as the LET function is a better way to write formulas in cells.
After confirming that this method works properly, register it in the Name Manager as a function that can be used for the entire book.

Set up the following formula as an example.

The red part is the argument definition, the blue part is the formula, and the green part is the actual value given.


The argument part can also be cell-specified.

It is also possible to specify a range of cells and use a tally function.


Register in Name Manager and make available to the entire book.

Click on Formulas and then on Name Managemer.

Click New.

Register the contents of your own functions.

NameSpecify a function name.
ScopeAvailable for the entire book or only for a specific sheet.
CommentSpecify a description.
Refers toSpecify the formula for the LAMBDA function. No validation argument is required.

Click OK to make the custom function available.

Effective Situations (Benefits).

It is now possible to write multiple required formulas in a book with unique function names, reducing the time and risk of omission of changes and simplifying the formulas.
This simplifies formulas by reducing the time required to make changes and the risk of omissions.

Situations where other functions are more appropriate

If the formulas are lined up horizontally or vertically, the Spill function is more effective.
LET function is more effective if you only need to name the formulas.

Helper functions.

The LAMBDA function can be combined with Helper functions to create more sophisticated formulas.

Recursive function

Although it is rarely used for general office purposes, it is possible to define a recursive function.a function that calls itself.

For example, to sum up a number from 1 to a specified number
The following function can be prepared. SumTotal is the function name.

=LAMBDA(value,IF(value>1,value + SumTotal(value-1),1))

Create your own functions available only in cells with the LET function.

For example, if the same formula appears many times in a cell, but the values or cell references are different, you may be able to simplify the formula by making it your own function.
In the following example, the tax amount is calculated using a custom function.




Microsoft Excel Functions Logical

Examples of Use

Formula to randomly extract a specified number of cells from a range of cells.

Formulas to extract text from a cell every specified number of letters.

Formulas to sort text in cells.

Formula to split text into cells one character at a time.

How to replace multiple texts with the SUBSTITUTE function.

Formula to split text into cells one character at a time.

Formula to extract only rows with values.

How to reverse text.

How to extract only numbers from text.

Calculating the median in a Pivot table. Its alternative method.