No Ticket Needed

Appearance

15 min fix 4 min read Recently reviewedBeginner

By , IT support technician

Quick Answer

Use the LET() function to assign a name to a repeating calculation so Excel only calculates it once. Use the LAMBDA() function (saved in the Name Manager) to create your own custom, reusable functions like =CALCTAX(A2).

How to Use LAMBDA and LET Functions to Clean Up Excel Formulas ​

Estimated Time to Fix: 15 minutes

Applies to: Microsoft Excel (Microsoft 365)
Article Type: How-To
Last Updated: 2026-07-21

Summary ​

If you write advanced Excel formulas, you know the pain of nested IF statements that are three lines long. It becomes impossible to read, and if you need to update a cell reference, you have to find and replace it five times in the same cell. The LET and LAMBDA functions solve this by letting you define variables and create your own custom functions.

Prerequisites ​

  • You must be using the Microsoft 365 subscription version of Excel.

Instructions ​

Part 1: How to use the LET function ​

The LET function allows you to assign a name to a calculation result, and then use that name later in the formula.

The Problem: Look at this formula: =IF(XLOOKUP(A2, Data!A:A, Data!B:B) > 100, XLOOKUP(A2, Data!A:A, Data!B:B) * 0.1, "No Bonus") You have to write the exact same XLOOKUP twice. Excel calculates it twice. If you need to change the column it searches, you have to change it twice.

The Solution (Using LET): You can define that XLOOKUP as a variable named Sales. =LET(Sales, XLOOKUP(A2, Data!A:A, Data!B:B), IF(Sales > 100, Sales * 0.1, "No Bonus"))

The Syntax:=LET(Name1, Value1, Name2, Value2, ... Calculation)

  1. Define a name (e.g., Sales).
  2. Tell Excel what that name equals (the XLOOKUP).
  3. Do your final calculation using the name.

Part 2: How to use the LAMBDA function ​

While LET cleans up a single cell, LAMBDA allows you to create an entirely new, custom function that you can use anywhere in your workbook.

Imagine you frequently have to calculate a complex shipping cost based on weight. Instead of copying a huge formula everywhere, you can create a custom function called =SHIPPING(weight).

Step 1: Test the LAMBDA in a cell The syntax is =LAMBDA(parameter1, parameter2, calculation). To test an 8% tax function on cell A1, you would write: =LAMBDA(price, price * 1.08)(A1)(The (A1) at the end tells Excel to feed cell A1 into the 'price' parameter for testing).

Step 2: Save it to the Name Manager Once you confirm the math works, you save it so Excel remembers it.

  1. Highlight and copy the formula, excluding the test parameter at the end. (Copy =LAMBDA(price, price * 1.08)).
  2. Go to the Formulas tab on the ribbon.
  3. Click Name Manager.
  4. Click New.
  5. In the Name box, type what you want to call your function (e.g., ADDTax).
  6. In the Refers to box at the bottom, paste your =LAMBDA(...) formula.
  7. Click OK and close the Name Manager.

Step 3: Use your new custom function Go to any blank cell in your workbook. Type =ADDTax(. Excel will suggest it exactly as it does a native function. Click cell A1 and press Enter. Excel will apply your custom math.

Troubleshooting ​

WARNING

LAMBDA functions only exist within the specific workbook where you created them in the Name Manager. If you open a brand new Excel file, your custom function will not be there unless you copy a sheet from the old workbook into the new one.

Symptom / ErrorPotential CauseSolution
#NAME? error when using LET or LAMBDAOld Excel versionThese functions do not exist in Excel 2016, 2019, or older perpetual licenses. You must use Microsoft 365.
Excel says "You've entered too few arguments for this function"Missing parametersIf you built a LAMBDA that requires two inputs (e.g., price and tax_rate), you must provide two cells when using the formula: =ADDTax(A1, B1).