The LET function in Google Sheets allows you to assign names to expressions, enabling you to reuse them multiple times in a formula. This provides two main advantages:

• Improved Readability: It makes complex formulas easier to understand, especially for others or for yourself when revisiting the sheet later.
• Enhanced Performance: By calculating an expression once and reusing it, you reduce the need for repeated calculations, improving performance.

Let me explain these points further:

Readability

Suppose we have a formula that returns the sum of a range of cells, such as total sales in a month:

=SUM(C:C)

We can use the IF function to check if the total sales meet our target. The formula might look like this:

=IF(SUM(C:C) < 100, "D", IF(SUM(C:C) < 200, "C", IF(SUM(C:C) < 300, "B", "A")))

With the LET function, you can assign SUM(C:C) a name (e.g., total_sales), simplifying the formula:

=LET(total_sales, SUM(C:C), IF(total_sales < 100, "D", IF(total_sales < 200, "C", IF(total_sales < 300, "B", "A"))))

This makes the formula easier to read and maintain since you only write SUM(C:C) once.

Performance Improvement

If you repeat the same expression in a formula, as with SUM(C:C) above, Google Sheets calculates it multiple times. This can slow down performance, particularly with complex or large datasets. LET calculates the expression once, improving performance.

Syntax of the LET Function in Google Sheets

Syntax:

LET(name1, value_expression1, [name2, value_expression2, …], formula_expression)

• name1, [name2, …]: Names assigned to expressions. Google Sheets treats these names as case-insensitive.
• value_expression1, [value_expression2, …]: The expressions to which the names refer.
• formula_expression: The final formula that uses the named values.

Note: Optional arguments in square brackets must be paired. If you use name2, you must also use value_expression2.

Examples of the LET Function in Google Sheets

Here are a couple of simple examples of how to use LET:

=LET(a, 5, a * 2)

Result: 10

In this formula, a is the name (name1), 5 is the assigned value (value_expression1), and a * 2 is the final calculation (formula_expression).

Here’s an example with two names and two value expressions:

=LET(a, 5, b, 2, a * b)

Result: 10

Real-Life Example of the LET Function in Google Sheets

While the examples above are simple, here’s a more practical use case.

Let’s say you want to sum sales amounts (C2:C7) in January 2023 (B2:B7) using SUMIF:

=ARRAYFORMULA(SUMIF(
   EOMONTH(B2:B7, -1) + 1, 
   DATE(2023, 1, 1), 
   C2:C7
))

Now, you want to categorize the result:

• “Poor” if it’s less than 10
• “Good” if it’s between 10 and 40
• “Exceptional” if it’s 40 or more.

A standard IF formula might look like this:

=IF(
   ARRAYFORMULA(SUMIF(
      EOMONTH(B2:B7, -1) + 1, 
      DATE(2023, 1, 1), 
      C2:C7
   )) < 10, "Poor", 
   IF(
      ARRAYFORMULA(SUMIF(
         EOMONTH(B2:B7, -1) + 1, 
         DATE(2023, 1, 1), C2:C7
      )) < 40, "Good", "Exceptional"
   )
)

This formula is not only long but also inefficient because Google Sheets calculates the SUMIF result twice. Using LET improves both readability and performance:

=LET(
   sales, 
   ARRAYFORMULA(SUMIF(
      EOMONTH(B2:B7, -1) + 1, 
      DATE(2023, 1, 1), 
      C2:C7
   )), 
   IF(sales < 10, "Poor", IF(sales < 40, "Good", "Exceptional"))
)

In this case:

• name1: sales
• value_expression1: ARRAYFORMULA(SUMIF(…))
• formula_expression: IF(sales < 10, "Poor", IF(sales < 40, "Good", "Exceptional"))

LET and LAMBDA

While you could replace the LET function with a LAMBDA, I recommend using them for different purposes. LAMBDA is mainly useful when creating custom functions, especially when combined with helper functions like BYROW, BYCOL, SCAN, and others.

Here’s how the previous formula would look with LAMBDA:

=LAMBDA(
   sales, IF(sales < 10, "Poor", IF(sales < 40, "Good", "Exceptional"))
)
(
   ARRAYFORMULA(SUMIF(
       EOMONTH(B2:B7, -1) + 1, 
       DATE(2023, 1, 1), 
       C2:C7
   ))
)

However, in this case, LET is simpler and more appropriate.

Types of Names You Can Use in the LET Function

We’ve explored how to use the LET function in Google Sheets, but what about naming conventions? Here are some guidelines:

• Alphanumeric: Combines letters and numbers (e.g., w, total1), but the name should not start with a number.
• Underscores: Use underscores to separate words (e.g., sales_total, avg_cost).
• Single Letters: Simple names for short expressions (e.g., x, y, z).
• Descriptive Names: Use clear names that describe values (e.g., area, cumulative).

Avoid using function names and cell references as names, and stick to a consistent naming style.

Conclusion

The LET function in Google Sheets enhances both the readability and performance of formulas by allowing you to name and reuse expressions. It’s especially helpful when working with complex calculations or repeating expressions, providing a clear advantage over traditional methods. While LAMBDA is more suited for advanced use cases, LET offers a simple and efficient solution for many common tasks.

You can also nest the LET function, which is particularly useful when using LAMBDA helper functions like REDUCE and MAP. For example, you can assign a name to the REDUCE formula with one LET and use another LET within the LAMBDA part of REDUCE.

Thanks for reading—happy spreadsheeting!