How To Use The LAMBDA Excel Function?
To use the LAMBDA function in Excel you need to have a Microsoft Office 365 subscription and the latest version of Microsoft Office installed. You also need the latest Microsoft updates installed (including Beta updates). Currently, you also need to join Microsoft Office Insiders in order to receive Beta updates.
The most basic use of the new LAMBDA excel function is to add if-then statements in your spreadsheets that only show up when a condition you’ve defined occurs, like below:
If [A] = [value], then show this value or else show this other value.
The above is written in Excel exactly how you would write it out in plain English. The “if-then” statement tells excel when to show the first value and when to show the second one so if cell A has a certain value, then that label will be shown instead of another. You can use any formula in the “if-then” statement, not just values.
The next step after writing your if-then statement is to define what values will trigger it! You can do this by using either of these methods:
Use an =IF() formula in a different cell and connect that cell reference to another cell with the LAMBDA excel function.
Use a Data Validation formula in another cell and connect that to your LAMBDA excel function so the input will come from what you select with the drop-down menu next time you open up your spreadsheet.
This is much easier than it sounds!
LAMBDA Excel Function Example
The next steps will show you an example of how to use the LAMBDA excel function with a real-life spreadsheet.
Let’s start with the Syntax:
=LAMBDA([parameter1, parameter2, …,] calculation)
The parameter argument defines the values that you want to pass to the calculation. This could be a cell reference, a text string, or a numeric value.
You can define up to 253 parameters, but this limit is unlikely to be reached!
The parameter argument is optional. Parameter names are defined as a text string e.g. x, number, myPar, etc.
The calculation argument defines the calculation that will be performed using the defined parameters. It must return a result, and it is not optional
=LAMBDA(x,x*10)(1)
This will return the result of 10 in the cell. A single parameter (x) is defined and the calculation argument multiplies that parameter by 10. The value of (1) after the first part of the function defines the value for the x parameter. This is only required if you are using the LAMBDA function directly into a cell instead of using it to create a re-usable function.
You can use standard Excel functions within the calculation argument.
=LAMBDA(x, y, SQRT((x^2+y^2)))
This LAMBDA function will use Pythagoras to calculate the hypotenuse of a right-angled triangle. The parameters x and y represent the horizontal and vertical sides of the triangle. The calculation argument squares both the parameters and adds them together and then uses the Excel SQRT function to get the value of the hypotenuse.
Creating Re-usable Function with LAMBDA Function In Excel
Re-usable LAMBDA functions are incredibly useful and can be used just like the Excel functions that exist in your everyday workbooks.
Get started by defining a new name then click on Formulas in the top Excel ribbon. This will give you the option to Define Name in the Defined Names group of the ribbon.
Give your new name a descriptive name, then click on the Scope button to define where you want this function to be available.
A workbook can contain up to 253 defined names (or functions). Each of these is either global or local in scope so by clicking one of these two buttons it will tell excel which setting you have chosen.
The final step is to define the parameters which you will need for your new function. These are simply text strings that can be used within custom functions just like standard excel formulas, so these could look something like x or number.
You now have a brand-new, re-usable Excel function! Be sure to save it with an appropriate name so that you can find it again later if needed!
You could then use this function in your spreadsheet by clicking on the cell where you want to see the results of your custom function, and entering =myFunction(parameter) into the formula bar. This will be displayed with a drop-down menu when using Data Validation so users can choose exactly which parameter they want to use as input for your custom function.
The LAMBDA Excel Function – A New Dawn In Excel
The LAMBDA function is a brand-new Excel tool that allows you to create flexible and reusable functions for your workbooks. From basic arithmetic, through Pythagoras’ Theorem, to complex conditional equations; the LAMBDA excel function opens up a whole new world of possibilities in Microsoft Office!
If you need help using the LAMBDA Excel function please get in touch with a member of our team. Our Excel Experts have worked with a range of businesses to transform their operations using automation, LAMBDA and more complex Excel features and functions.
