Skip to Content

Learn Excel LET Function (Formula, Syntax, Examples)

The Excel LET function lets you define named variables within a formula. It is mostly used within complex and lengthy formulas to assign names to values or calculations. Its main goal is to make such formulas more readable and easier to understand.

For example, suppose that you are a sales manager. You plan to offer a quarterly bonus for each salesperson. Here’s the data:

The bonus needs to be calculated based on the following logic,

  • 10% bonus if the total sale is more than $300,000
  • 5% bonus if the total sale is between $250,000 and $300,000
  • No Bonus if the total sale is less than $250,000

Here is a formula to calculate a quarterly bonus:

=IF(SUM(B2:D2)>=300000, SUM(B2:D2)*0.1, IF(SUM(B2:D2)>=250000, SUM(B2:D2)*0.05, "Better Luck Next Time"))

The above formula is both slightly complex and lengthy. We can make it simpler using the LET function.

=LET(TotalSales, SUM(B2:D2),
   IF(TotalSales>300000, TotalSales*0.1,
   IF(TotalSales>=250000, TotalSales*0.05,
   "Better Luck Next Time"
       )
     )
)

Where,

  • TotalSales is the named variable assigned to the formula SUM(B2:D2), which calculates the total sales generated by the given sales rep.
  • TotalSales>300000 is the logical expression used to identify if the quarterly sale is greater than $300,000.
  • TotalSales*0.1 is the formula to calculate the 10% bonus for eligible sales reps.
  • TotalSales>=250000 is the logical expression used to identify if the quarterly sale is greater than or equal to $250,000.
  • TotalSales*0.05 is the formula to calculate a 5% bonus for eligible sales reps.
  • Better Luck Next Time is the desired output if the given sales rep couldn’t get a bonus.

So, using a single named variable, the entire formula becomes more comprehensible. You no longer need to remember the underlying logic.

Stay tuned because in the upcoming sections of this article, we will include multiple names (named variables) to simplify complex formulas.

Syntax

The LET is a prebuilt logical function in Excel with the following syntax. It is mostly combined with the other functions to simplify formulas.

=LET(name1, name_value1, calculation_or_name2, [name_value2, …)

Where,

  • name1 is the first name to assign
  • name_value1 is the value or calculation you wish to assign to the first name
  • calculation_or_name2 is an optional argument that can be the second name or calculation that uses the first name
  • name_value2 is the value or calculation that will be assigned to the second name

Important Notes:

  • Names must begin with a letter
  • You can define up to 126 name-value pairs
  • Names are not case-sensitive
  • Name and values are calculated once and reused as many times as needed within the formula
  • Each named variable is calculated in sequence
  • Avoid using cell references as names
  • There should be a calculation at the end that uses all the named variables
  • The calculation at the end returns final results

How to use the LET Function in Excel

Download the example spreadsheet used to demonstrate the LET function in the upcoming section. Practice is the key to success!

Download Now

TASK

Suppose that you are an HR Manager. The sales team has forwarded you the following details.

They plan to send this data every month. They want you to calculate the monthly salary based on the following factors:

  • Sales target achievement
  • Assigned region
  • Years of service

Commissions based on sales target achievement are as follows:

  • 50% of their base salary if the achievement is greater than 120%
  • 25% of their base salary if the achievement is greater than 100%
  • 10% of their base salary if the achievement is greater than 75%
  • 0% commission if the achievement is below 75%

Bonus based on region:

  • $5,000 for North
  • $4,000 for South
  • $2,000 for East
  • $1,000 for West

Loyalty bonus based on years of service

The loyalty bonus is $1,000 per month if the employee has completed 1 year in our company. It increases by $1,000 each year for the next 10 years.

STEP 1: Build A Core Formula

We will first calculate the incentive (additional amount based on the above conditions) using the IF function.

Our formula would be:

=IF(D2>=120%, B2*0.5, IF(D2>=100%, B2*0.25, IF(D2>=75%, B2*0.1, 0))) + XLOOKUP(C2, {"North", "South", "East", "West"}, {5000, 4000, 2000, 1000}) + MIN(E2*1000, 10000)

Where,

  • IF(D2>=120%, B2*0.5, IF(D2>=100%, B2*0.25, IF(D2>=75%, B2*0.1, 0))) is the nested IF statement to calculate commission based on sales target achievement
  • XLOOKUP(C2, {“North”, “South”, “East”, “West”}, {5000, 4000, 2000, 1000}) is the formula to calculate the bonus based on the region
  • MIN(E2*1000, 10000) is the formula to calculate loyalty bonus based on years of service

If we put the above formula in column F, we will get the incentive as shown below.

STEP 2: Create the LAMBDA Version of the Core Formula

Note that the above formula needs to be used on a monthly basis to calculate the final salary based on the data provided by the sales team. So, instead of manually typing this lengthy formula each month, let us create a custom function that will get the job done within a few seconds.

To learn more about the LAMBDA function, click here.

It requires parameters and a calculation. We already created a formula (calculation) in the previous step. Now, let us get the parameters.

  • base – It is the parameter for the base salary
  • experience – It is the parameter for the years of experience
  • region – It is the parameter for the region of sales
  • achievement – It is the parameter for the sales target achievement.

Our Lambda version of the formula will be:

=LAMBDA(base, region, achievement, experience, IF(achievement>=120%, base*0.5, IF(achievement>=100%, base*0.25, IF(achievement>=75%, base*0.1, 0))) + XLOOKUP(region, {"North", "South", "East", "West"}, {5000, 4000, 2000, 1000}) + MIN(experience*1000, 10000))

If we put this formula as is in column F, we will get an error.

This error is caused by the function being unable to find the parameters. The solution is to add the cell references to the formula as shown below.

=LAMBDA(base, region, achievement, experience,
    IF(achievement>=120%, base*0.5,
    IF(achievement>=100%, base*0.25,
    IF(achievement>=80%, base*0.1, 0)))
    +
    XLOOKUP(region, {"North", "South", "East", "West"}, {5000, 4000, 2000, 1000})
    +
    MIN(experience*1000, 10000)
)(B2,C2,D2,E2)

Now, it works fine.

STEP 3: Simplify the Formula Using the LET Function

The above LAMBDA formula is both complex and hard to remember. Let us use the LET function to simplify it.

=LAMBDA(base, region, achievement, experience,
    LET(
      commission,
            IF(achievement>=120%, base*0.5,
            IF(achievement>=100%, base*0.25,
            IF(achievement>=80%, base*0.1, 0))),
      regionBonus,
            XLOOKUP(region, {"North", "South", "East", "West"}, {5000, 4000, 2000, 1000}),
      loyaltyBonus,
            MIN(experience*1000, 10000),
      total,
            commission + regionBonus + loyaltyBonus,
      total
    )
)

Where,

  • commision is the first name (named range)
  • IF(achievement>=120%, base*0.5, IF(achievement>=100%, base*0.25, IF(achievement>=80%, base*0.1, 0))) is the calculation assigned to the first name
  • regionBonus is the second name (named range)
  • XLOOKUP(region, {“North”, “South”, “East”, “West”}, {5000, 4000, 2000, 1000}) is the calculation assigned to the second name
  • loyaltyBonus is the third name (named range)
  • MIN(experience*1000, 10000) is the calculation assigned to the third name
  • total is the fourth name (named range)
  • commission + regionBonus + loyaltyBonus is the formula assigned to the fourth name

You will see the total at the end of the above formula. It simply tells the function to display the value of the named range (variable) total as the output.

STEP 4: Create a Custom Function

This is the final step, where we will name our entire formula. That name will be reused in the future to calculate the incentive within a few seconds.

We will use the Name Manager tool in Excel. It’s a built-in tool in Excel that lets you define custom names for ranges, formulas, and constants.

You can easily manage user-defined names within the given workbook by going to Formulas > Defined Names > Name Manager. Refer to the following screenshot.

For complete steps, read our article: Excel LAMBDA Function – An Ultimate tool to Create Custom Functions in Excel.

Benefits of Using the LET Function

Excel is the top spreadsheet application for complex data analysis. It allows us to create complex formulas that help us automate repetitive tasks.

But what if you struggle to recall the logic behind a complex formula that was created a year ago?

In this case, the LET function has got your back. It lets you name each value or calculation within the given formula. Here are the key benefits of using the LET function in Excel:

  • Simplification: By naming the lengthy calculations in a formula, you avoid redundancy and errors. You can use those short and simplified names any time within the given formula.
  • Clarity: Anyone from your team can easily read and understand the logic behind the given formula with named variables.
  • Speed: Due to redundant code, the overall calculation speed increases significantly.

Takeaway

Learning the LET function in Excel is a crucial step. It helps you simplify complex formulas. It is mostly combined with the LAMBDA function in Excel.

Make sure to follow the naming guidelines while using this function. Please comment below if you are stuck or encounter any particular error while using the LET function in Excel. I will answer your questions as soon as possible.

Additional Resources:

Get an Office 365 Subscription to Access all the powerful Functions and Tools in Excel.