What’s a LAMBDA?
LAMBDA is not just another Excel function – It is the mother of all Excel functions.
Why is LAMBDA so special?
LAMBDA can be used to create absolutely-new functions, tailor-made for your needs. The new function relies on one or more existing Excel functions & parameters that you define. After you create your LAMBDA function, you can use it anywhere in your file!
In the past, to create a new function, you would need to use VBA User Defined Functions (UDFs). It required you to use a different programming language (VBA) instead of Excel, and it was quite unfriendly!
Today, using LAMBDA, life is easier.
Understanding how LAMBDA works – step-by-step example
OK, enough with the mumbo-jumbo – Let’s start with a real-life example.
Please don’t give up while reading the explanation below – LAMBDA is a difficult concept to grasp, but once we’ll go through a full example – It will be much easier. We promise.
Let’s assume that we wanted to invent a function that converts miles to kilometers, instead of dividing by 1.609344 each time. We can do it using LAMBDA.
Let’s answer the following question – how many kilometers are 5 miles?
Let’s write our first LAMBDA:
If you wish to copy the LAMBDA above, here’s the formula:
=LAMBDA(mile,mile*1.609344)(5)
LAMBDA syntax explained
The syntax of LAMBDA is basically:
=LAMBDA(parameter_or_calculation, …)
parameter_or_calculation – As we are creating a new function, we need to tell Excel what will be the arguments used by the function. We can assign each argument a name, that will allow us (and the users) to easily understand what’s the purpose of the argument. We can call our argument mile, as we want the user to type a number that represents miles.
After we finish declaring our argument (or arguments – we can have many of them!), we put a comma, and type the calculation that we wish the function will perform – mile * 1.609344.
Then – after we finish inventing our new function, we close the brackets of the LAMBDA, and open a new pair – and there we type the number 5 – which was the number that we wish to use within the mile argument.
Easy, right?
Nope. Not easy. Not yet. After seeing this example – you might ask yourself – Why on earth should I use LAMBDA at all?! It’s longer than just writing something like =A1*1.609344. And you will be correct. The true power of LAMBDA is unleashed when we take the function we wrote before, and store it as a new function within our workbook. Imagine that you could type something like this in Excel to find out how many kilometers are 5 miles…
=MileToKm(5)
No need to imagine. You can do it 🙂
How did we do it? Simply! We saved our LAMBDA in Excel’s Name Manager – allowing us to use the new function anywhere in our workbook. To do so, we go to Formula tab -> Name Manager -> Type a nice name for our new function, and in “refers to:” we paste the LAMBDA function we created before (without the (5)):
LAMBDA scope & other important stuff to know…
- Scope – The LAMBDA function is saved within your workbook. If you send the Excel file that stores this LAMBDA – Anyone with the file will be able to use the newly created function as well. But only in their workbook. The LAMBDA will not work for any other workbook except the one where it was stored.
- Do all Excels support LAMBDA? LAMBDA works in Excel 365 and Excel for the Web. If you have previous versions of Excel, you won’t be able to use it.
- Can I use more than one argument for my LAMBDAs? Definitely. You can create functions with dozens of arguments – separate each of these by a comma. Just remember to type the calculation itself in the last part before you close the brackets. For example:
=LAMBDA(argument1,argument2,argument3,argument1*argument2/argument3)
- Name Manager works in Excel desktop and is not available in Excel for the Web. To overcome this, and to write LAMBDAs in a more elegant way, you can use the Advanced Formula Environment add-in – Here’s a great tutorial on how to use it!
Practice LAMBDA
Let’s try to create our first LAMBDA ourselves 🙂