Skip to content
Tutorials Center
My learning

Topics

Lesson 21 of 29 ยท Advanced Formulas

Array Formulas and ARRAYFORMULA

Learn array formulas in Google Sheets: ARRAYFORMULA for whole-column results, plus MAP, BYROW, LAMBDA and LET for clean, powerful formulas.

Most formulas return one value for one cell. An array formula works on a whole range at once and can return many values that spill into the cells below and to the right. Instead of copying =B2*C2 down 500 rows, you write one formula in D2 that fills the entire column, and new rows are covered automatically.

This lesson explains ARRAYFORMULA, how arrays behave in ordinary functions, and the newer helper functions MAP, BYROW, LAMBDA and LET. Google Sheets cannot run here, so formulas are read-only and the JavaScript stand-ins model what each function does.

One formula, many results

Write once A single formula in the top cell.
It spills Results fill the cells below.
Stays current New rows are included automatically.
1 / 2

The spill

When a formula returns an array, Sheets writes the first value into the formula cell and spills the others into the cells next to it. Those cells must be empty. If something is in the way, the formula shows #REF! with a message that the array result was not expanded. Delete the blocking cell and the result appears.

A range itself is an array. =SORT(A2:A10) already spills, and so do FILTER, UNIQUE and SPLIT. The functions that are not array-aware, such as IF, LEFT or * on ranges, need help, and that is what ARRAYFORMULA is for.

ARRAYFORMULA

Wrap a normal formula in ARRAYFORMULA(...) and give it ranges where you used single cells:

TEXT
=ARRAYFORMULA(B2:B * C2:C)

Put this in D2 and it multiplies every price in B by every quantity in C, down the whole column. Text and logic work the same way:

TEXT
=ARRAYFORMULA(UPPER(A2:A))
=ARRAYFORMULA(IF(B2:B >= 50, "Pass", "Fail"))

There is one problem. An open range such as B2:B includes empty cells, so the formula also spills results for blank rows: 0 for the product, "Fail" for the condition. Guard against it with IF(B2:B="", "", ...):

TEXT
=ARRAYFORMULA(IF(B2:B="", "", IF(B2:B >= 50, "Pass", "Fail")))

A related trick puts a header and the formula together so the whole column is self-contained, with { } stacking vertically:

TEXT
={"Total"; ARRAYFORMULA(IF(B2:B="", "", B2:B * C2:C))}

Inside ARRAYFORMULA, IF compares element by element. Outside it, IF(B2:B>=50, ...) would look only at the first cell. Some functions summarize a range into one value, for example SUM(B2:B). Those do not become arrays, and that is by design.

JS Stand-in: the same formula over a whole column

Press Run (or Ctrl + Enter) to see the console output here.

No output.

LAMBDA: a function with no name

LAMBDA creates a small function inside a formula. Its parameters come first and the calculation is the last argument:

TEXT
=LAMBDA(x, x * 2)(5)

The (5) calls it right away and returns 10. On its own this is not very useful, but lambdas are the pieces you pass into MAP, BYROW, BYCOL, REDUCE, SCAN and MAKEARRAY. You can also store one as a named function (Data โ†’ Named functions) and use it like a built-in, for example TAX(price).

MAP, BYROW and friends

MAP(array, LAMBDA(item, ...)) applies the lambda to every item and returns an array of the same shape. It is clearer than ARRAYFORMULA when the logic has several steps or when a function does not accept arrays.

TEXT
=MAP(B2:B10, LAMBDA(price, ROUND(price * 1.2, 2)))

BYROW(range, LAMBDA(row, ...)) gives the lambda a whole row at a time and returns one result per row. That solves a classic problem: SUM and MAX turn a range into one number, but you may want one per row:

TEXT
=BYROW(B2:D10, LAMBDA(row, MAX(row) - MIN(row)))

BYCOL is the same idea for columns. Two more are worth knowing:

  • REDUCE(initial, array, LAMBDA(acc, item, ...)) folds an array into one value, such as a running product.
  • SCAN(initial, array, LAMBDA(acc, item, ...)) is like REDUCE but returns every step, which makes a running total.
TEXT
=SCAN(0, B2:B10, LAMBDA(total, x, total + x))

Because the lambda parameters are local names, they do not clash with cells. Use short, meaningful names such as price, row or acc. Remember that a lambda needs a value for each parameter, otherwise Sheets reports an error about the number of arguments.

JS Stand-in: MAP, BYROW, SCAN and REDUCE

Press Run (or Ctrl + Enter) to see the console output here.

No output.

LET: name a calculation

LET(name1, value1, [name2, value2, ...], result) assigns names to values or calculations, then uses them in the result. It avoids repeating a long expression, which makes the formula faster to read and, because each value is computed once, often faster to run.

Without LET:

TEXT
=IF(SUM(B2:B10)/COUNT(B2:B10) > 50, SUM(B2:B10)/COUNT(B2:B10), "low")

With LET:

TEXT
=LET(avg, AVERAGE(B2:B10), IF(avg > 50, avg, "low"))

You can chain names, and a later name may use an earlier one:

TEXT
=LET(
  total, SUM(B2:B10),
  n, COUNT(B2:B10),
  avg, total / n,
  IF(avg > 50, avg, "low")
)

The line breaks are allowed in the formula bar (press Ctrl+Enter or Shift+Enter inside it to add one) and they make a formula readable. LET and LAMBDA combine well: inside a lambda, LET can name intermediate steps.

JS Stand-in: LET as named steps

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Thinking in arrays

A few habits help.

  • Prefer one formula per column at the top of a table, using an open range such as B2:B, instead of 1,000 copies. Remember the guard for blanks.
  • Check the shape. Arithmetic on two arrays needs the same dimensions, or one of them must be a single row or column. Mismatched sizes produce errors or #N/A values.
  • Combine with FILTER, SORT and UNIQUE. =SORT(UNIQUE(FILTER(A2:A, B2:B > 0))) is a whole pipeline in one cell (see Sorting and Filtering Data).
  • Use QUERY for table-like questions. Selecting, filtering and grouping in one expression is often easier than building it from functions: see The QUERY Function.
  • Avoid full-column ranges in heavy array formulas on big sheets. Use an exact range when you know it.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does the formula =ARRAYFORMULA(B2:B * C2:C) in D2 do?

    ARRAYFORMULA applies the multiplication element by element and fills the cells below.
  2. 2. Your array formula shows #REF! saying the result was not expanded. What is the likely cause?

    Spilled results need empty cells; anything in the way blocks the array.
  3. 3. Which function returns one result per row of a range?

    BYROW passes each row to a lambda and returns one value per row. MAP works item by item.
  4. 4. What is the main benefit of LET?

    LET assigns names to values or calculations, making formulas shorter, clearer and often faster.

Summary

  • An array formula returns many values that spill into empty cells; one formula can cover a whole column.
  • ARRAYFORMULA makes ordinary functions and operators work element by element; guard blank rows with IF(range="", "", ...).
  • LAMBDA is an unnamed function; MAP, BYROW, BYCOL, REDUCE and SCAN apply one to arrays.
  • LET gives names to intermediate results for readable formulas.
  • Check the shape of arrays and keep the spill area empty.

Next: The QUERY Function