Using LET with ARRAYFORMULA in Google Sheets for Cleaner Logic

A user on a Google Sheets help forum recently posted a common frustration: “I’m trying to use the new LET function to simplify a complex calculation, but as soon as I wrap it in ARRAYFORMULA, it stops working for the whole column and only calculates the first row. How do I get LET variables to behave like arrays?”

This “single-row syndrome” usually happens because of how Google Sheets handles range definitions within the LET function. Here is how to master the combination of ARRAYFORMULA, LET, and IF to create professional-grade spreadsheets.

The Minimal Working Example

To use these three together, you must ensure your variables in the LET function are defined as ranges, and the ARRAYFORMULA is wrapped around the logic that needs to iterate.

The Scenario: You have a list of sales figures in Column A, and you want to calculate a 10% tax, but only if the sale is over $100.

The Formula:

=ARRAYFORMULA(
  LET(
    sales_range, A2:A,
    tax_rate, 0.10,
    IF(sales_range = "",,
      IF(sales_range > 100, sales_range * tax_rate, 0)
    )
  )
)

Tested on Google Sheets as of 2024.

Why This Works

When you combine these functions, you are essentially building a small “program” inside a single cell:

1. LET: This assigns names to values or ranges. By naming A2:A as sales_range, you can reference it multiple times without typing the range over and over. This makes the formula significantly easier to read and debug.
2. ARRAYFORMULA: This tells Google Sheets to look at the ranges inside the formula and process them row-by-row instead of just looking at the first cell.
3. IF (The Empty Check): IF(sales_range = "",, ...) is a crucial pattern. It tells the formula: “If the cell in Column A is empty, leave this cell empty.” Without this, your array formula might fill thousands of rows with zeros or errors all the way to the bottom of the sheet.

Breakdown of Arguments

Function	Argument in Example	Purpose
LET	sales_range, A2:A	Defines the scope. Use a full range (A2:A) to ensure it works with the array.
IF	sales_range = ""	Prevents the formula from running on empty rows (keeps the sheet clean).
IF	sales_range > 100	The actual logic/condition you are testing.
ARRAYFORMULA	Wrapping the whole block	Forces the logic to spill down the column automatically.

Adapting to Your Data

To use this for your own projects, follow these steps:

1. Define your inputs first: Inside the LET function, list your ranges and constants (like tax rates or discount percentages).
2. Check for blanks: Always start your calculation logic with IF(your_range = "",, ...) to prevent “ghost rows” from appearing at the bottom of your sheet.
3. Use the Range Reference: Ensure that any variable you defined as a range (e.g., A2:A) is treated as such in your math. If you only point to A2, the ARRAYFORMULA won’t have anything to iterate over.
4. Format the Output: Select the column and go to Format > Number to ensure your array results look correct (e.g., Currency or Percent).

Common Questions

Can I use this for complex text manipulation?

Yes. You can define a variable for a string of text in Column A, then use LET to SUBSTITUTE or REGEXREPLACE that variable. Because it is wrapped in ARRAYFORMULA, it will clean the entire column at once.

What if I need to use more than one column?

You can define multiple variables. For example: LET(qty, A2:A, price, B2:B, IF(qty="",, qty * price)). This keeps your formulas from becoming “spaghetti code” where ranges like A2:A and B2:B are repeated dozens of times.

Is this better than using the MAP function?

While MAP and LAMBDA are also powerful tools for handling arrays, the ARRAYFORMULA(LET(...)) combo is often faster for Google Sheets to calculate on very large datasets (10,000+ rows) because it uses the built-in array engine rather than a custom lambda loop for every row.

How do I handle errors if a cell contains text instead of a number?

You can wrap your calculation in IFERROR. For example: IFERROR(sales_range * tax_rate, 0). This ensures that if someone accidentally types “N/A” in your sales column, your formula returns a 0 instead of a #VALUE! error.