LAMBDA lets you define a custom function directly in a cell by specifying parameters and a calculation, so you can reuse complex logic without VBA. It returns the result of that calculation, and you can name and save it for use anywhere in the workbook.
=LAMBDA([parameter1], …, calculation)What does LAMBDA do?
LAMBDA is Excel's way of letting you build your own functions using regular formulas. Instead of repeating the same calculation in many cells, you define it once with parameters and a calculation, and then call it like any built-in function. For example, =LAMBDA(x, x*2)(5) returns 10.
This matters because most spreadsheet work involves repeated logic: applying a discount, converting units, cleaning text, or scoring results. Before LAMBDA, you either copied long formulas or used VBA. LAMBDA keeps everything in Excel formulas, works with dynamic arrays, and can be named and reused across the workbook.
A typical situation: you manage a sales sheet and need to apply a tiered discount based on order size. You write the logic once as a LAMBDA, test it, then use it in every row. When the rules change, you update the LAMBDA definition in one place instead of editing dozens of formulas.
When to use LAMBDA
- Applying the same discount or markup rule to many rows
- Converting units (e.g., kg to lb) consistently across a sheet
- Cleaning text (e.g., trimming and proper-casing names) in multiple columns
- Calculating grades or scores with custom thresholds
- Creating a reusable tax or fee calculator for invoices
- Building a named function for a team to standardise calculations
LAMBDA syntax
=LAMBDA([parameter1], …, calculation)| Argument | Required? | What to enter |
|---|---|---|
parameter1 | Optional | A name for an input value the function will accept. You can have up to 253 parameters. Example: x in =LAMBDA(x, x*2)(5). |
calculation | Required | The formula that uses the parameters and returns a result. It must be the last argument. Example: x*2. |
What it returns: The result of the calculation after the parameters are replaced with the values you supply when you call the LAMBDA. If you name the LAMBDA, it becomes a reusable custom function.
LAMBDA examples
1 Double a number
This simple example shows the core idea: define a parameter x and a calculation x*2. The LAMBDA is called immediately with the value 5, so it returns 10. In a sheet, you can replace 5 with a cell reference to double any number.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Number | |||
| 2 | 5 | 10 | ||
| 3 | 8 | |||
| 4 | 12 | |||
| 5 |
2 Apply a 10% discount to a price
Here the LAMBDA takes a price and returns the discounted amount. The calculation p*0.9 reduces the price by 10%. Calling it with 200 returns 180. You can use a cell reference instead of 200 to apply the discount to any price in your table.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Item | Price | |||
| 2 | Pen | 200 | 180 | ||
| 3 | Notebook | 150 | |||
| 4 | Backpack | 400 | |||
| 5 |
3 Convert kilograms to pounds
This LAMBDA converts a weight in kilograms to pounds using the factor 2.20462. The parameter kg is multiplied by the factor in the calculation. Calling it with 10 returns 22.0462. You can replace 10 with a cell reference to convert any weight.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Weight (kg) | |||
| 2 | Sugar | 10 | 22.0462 | ||
| 3 | Flour | 25 | |||
| 4 | Rice | 5 | |||
| 5 |
4 Clean and capitalise a name
This LAMBDA removes extra spaces and converts a name to proper case. The calculation uses TRIM and PROPER on the parameter txt. Calling it with " john smith " returns "John Smith". This is useful for standardising names imported from other systems.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Raw Name | |||
| 2 | john smith | John Smith | ||
| 3 | mary jane | |||
| 4 | robert brown | |||
| 5 |
5 Tiered discount based on order amount
This LAMBDA applies a discount that depends on the order amount: 10% for orders of 1000 or more, 5% for orders of 500 or more, and no discount otherwise. The calculation uses IF to check the thresholds. Calling it with 1200 returns 1080. You can use a cell reference to apply the rule to each order.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order ID | Amount | |||
| 2 | 1001 | 1200 | 1080 | ||
| 3 | 1002 | 800 | |||
| 4 | 1003 | 300 | |||
| 5 |
Common LAMBDA errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #VALUE! | The calculation uses a parameter that was not supplied when calling the LAMBDA, or the wrong number of arguments is passed. | Check that the number of values you pass matches the number of parameters. For example, =LAMBDA(x, x*2)(5) has one parameter and one value. |
| #CALC! | The LAMBDA is missing its calculation argument, or the calculation returns an error or an empty array. | Ensure the last argument is a valid calculation. Test the calculation separately with sample values. |
| #NAME? | You tried to call a named LAMBDA that does not exist or is misspelled. | Define the name in the Name Manager or correct the spelling. Named LAMBDAs must be created before use. |
| #NUM! | A parameter name is not valid (e.g., starts with a number or contains spaces). | Use valid names: start with a letter, use letters, numbers, and underscores only, and avoid names that look like cell references. |
Tips for using LAMBDA
- Test your LAMBDA with sample values before using it across many rows.
- Use the Name Manager to save a LAMBDA as a named function for reuse.
- Keep parameter names short and descriptive, like
priceorqty. - Remember that LAMBDA is available in Excel for Microsoft 365 and Excel 2021 and later.
- Combine LAMBDA with
MAPorBYROWto apply it to ranges without dragging formulas.
Frequently asked questions
What is the difference between LAMBDA and a regular formula?
A regular formula is written for a specific cell. LAMBDA lets you define a reusable function with parameters, so you can call it with different values and avoid repeating the same logic.
Can I use LAMBDA without naming it?
Yes. You can call a LAMBDA immediately by adding the arguments in parentheses after it, like =LAMBDA(x, x*2)(5). Naming it makes it reusable across the workbook.
Does LAMBDA replace VBA?
Not entirely. LAMBDA handles many custom calculation needs without macros, but VBA is still needed for automation, events, and interacting with the Excel object model.
Why do I get a #CALC! error with LAMBDA?
This often means the calculation argument is missing or returns an error. Check that you provided a valid calculation as the last argument.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. LAMBDA reference