How to Use the LET Function to Simplify Complex Formulas in Excel

The Problem with Repetitive Formulas

As Microsoft Excel users advance in their skills, their formulas tend to become incredibly long and complex. It is common to see a single cell containing a massive, nested formula that performs the exact same VLOOKUP or FILTER calculation three or four times within the same string.

For example, a traditional formula to check if a specific VLOOKUP result is greater than 100 looks like this:
=IF(VLOOKUP(A1, D1:F100, 3, FALSE) > 100, VLOOKUP(A1, D1:F100, 3, FALSE) * 0.1, VLOOKUP(A1, D1:F100, 3, FALSE))

Notice how the exact same VLOOKUP expression is written three times. Not only does this make the formula nearly impossible to read or debug, but Excel actually has to physically compute that VLOOKUP three separate times, slowing down the performance of large workbooks.

The LET function solves this by allowing you to define a variable, assign a calculation to it, and then simply use that short variable name throughout the rest of your formula.

Understanding the Syntax

The syntax for the LET function requires pairs of names and values, followed by one final calculation.

=LET(name1, value1, [name2], [value2], ..., final_calculation)

• name1: The custom name you want to assign to your variable (e.g., “Result”, “TotalSales”, “x”). The name cannot contain spaces.
• value1: The actual calculation or cell reference that generates the data (e.g., the VLOOKUP formula).
• final_calculation: The final math or logic you want to execute, using the custom names you just created.

Example 1: Simplifying an IF Statement

Let’s rewrite the terrible, repetitive VLOOKUP formula from the introduction using the LET function.

We will create a variable named SalesAmount and assign the VLOOKUP to it. Then, we write our simple IF statement.

=LET(SalesAmount, VLOOKUP(A1, D1:F100, 3, FALSE), IF(SalesAmount > 100, SalesAmount * 0.1, SalesAmount))

Why is this better?

1. Readability: The IF statement at the end is incredibly easy to read: If SalesAmount is greater than 100, multiply it by 0.1, otherwise just output the SalesAmount.
2. Performance: Excel executes the VLOOKUP exactly one time, stores the answer in memory as SalesAmount, and reuses that memory multiple times in the final calculation. This drastically improves spreadsheet calculation speeds.
3. Maintenance: If the range of your lookup table changes from D1:F100 to D1:Z500, you only have to update the VLOOKUP in one single place, rather than carefully replacing it three times.

Example 2: Using Multiple Variables

The LET function supports up to 126 variable pairs. You can even use the result of your first variable to calculate your second variable.

Imagine you want to calculate a final price after a discount, but only if a specific date condition is met. You can create multiple clean variables:

=LET(BasePrice, SUM(A1:A10), DiscountRate, 0.15, DiscountAmount, BasePrice * DiscountRate, BasePrice - DiscountAmount)

In this formula:

• BasePrice is the sum of A1:A10.
• DiscountRate is hardcoded to 15%.
• DiscountAmount mathematically relies on the first two variables.
• The final calculation simply subtracts the DiscountAmount from the BasePrice.

By adopting the LET function, you transition from writing messy spreadsheet hacks to writing clean, professional, programmer-style logic.