Google Sheets ARRAYFORMULA: How to Use It Without Breaking Your Sheet

ARRAYFORMULA is one of those Google Sheets features that feels like magic once you understand it and feels like chaos before you do. It applies a formula to an entire range at once, returning multiple results from a single formula cell. No dragging formulas down a column, no risk of rows getting out of sync if someone inserts a row in the middle.

The problem: it interacts badly with several other things — manual entries in the same column, other array formulas, and a handful of functions that simply don’t work inside it. This guide is the version you’d write after getting burned by all of those.

What ARRAYFORMULA Actually Does

Normally, a formula in one cell produces one result. ARRAYFORMULA makes a formula produce multiple results — one per row — from a single formula cell. The result spills down automatically.

=B2*C2                       → one result, in one cell
=ARRAYFORMULA(B2:B*C2:C)     → one formula, results in every row

The formula sits only in the first cell. Everything below it is output, not an editable cell.

The Keyboard Shortcut Worth Knowing

Instead of typing ARRAYFORMULA(), press Ctrl+Shift+Enter (Windows) or Cmd+Shift+Enter (Mac) after writing your formula. Google Sheets wraps it in ARRAYFORMULA() automatically.

ARRAYFORMULA + IF: Applying Logic to an Entire Column

The most common use case — conditional logic without copying a formula down:

=ARRAYFORMULA(IF(A2:A="Approved", "Yes", "No"))

With a numeric condition, applying a 10% discount to values over 100:

=ARRAYFORMULA(IF(B2:B>100, B2:B*0.9, B2:B))

ARRAYFORMULA + VLOOKUP: Lookup an Entire Column

Standard VLOOKUP looks up one value at a time. Wrap it in ARRAYFORMULA to look up every value in a column at once:

=ARRAYFORMULA(VLOOKUP(A2:A, E:F, 2, 0))

To handle unmatched values without errors:

=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A, E:F, 2, 0), ""))

Note: XLOOKUP does not work inside ARRAYFORMULA — it’s already array-capable and handles entire columns natively. Use VLOOKUP (with ARRAYFORMULA) or XLOOKUP (without ARRAYFORMULA).

ARRAYFORMULA + COUNTIF: Count Across Rows

COUNTIF doesn’t natively return per-row counts when given a range as the criteria. ARRAYFORMULA fixes that:

=ARRAYFORMULA(COUNTIF(B2:B, A2:A))

For each value in A2:A, this counts how many times it appears in B2:B — one count per row.

What Breaks Inside ARRAYFORMULA

The Conflict That Corrupts Your Data Silently

ARRAYFORMULA breaks silently when a manual entry exists anywhere in the column it’s trying to fill. If your ARRAYFORMULA is in D2 and someone types a value in D47, the array stops at D46 and picks back up at D48 — with no error message.

This is the most common complaint about ARRAYFORMULA in shared sheets: ‘the formula stopped working halfway down the column’ is almost always a manual entry in the output range.

When Not to Use ARRAYFORMULA

ARRAYFORMULA processes every row every time anything in the sheet changes. On large sheets with many ARRAYFORMULA columns doing complex lookups, this recalculation overhead becomes noticeable. If your sheet has 10,000+ rows and multiple ARRAYFORMULA columns, a script that runs on-demand is likely faster.

Also avoid ARRAYFORMULA in columns where users need to enter different values in different rows — the array formula locks the entire column to its own output, making manual entry impossible.

A Complete Example: Automatic Status Column

A project tracker where status in column E should automatically update based on other columns — without staff filling it in manually.

Logic: If D (due date) has passed and C (completion) is blank → ‘Overdue’. If C is filled → ‘Complete’. Otherwise → ‘In Progress’.

=ARRAYFORMULA(
  IF(A2:A="", "",
    IF(C2:C<>"", "Complete",
      IF(D2:D

The outer IF(A2:A="", "", ...) ensures no output appears in empty rows. The formula auto-extends as new rows are added. One formula in E2 handles the entire column.