Why does my ARRAYFORMULA only return the first result instead of the full array?

This usually means you're using a function that doesn't support array output, most commonly VLOOKUP. VLOOKUP inside ARRAYFORMULA returns only the first match. Replace it with `=ARRAYFORMULA(IFERROR(INDEX(lookup_range, MATCH(A2:A100, key_range, 0)), ""))`. The INDEX/MATCH combination handles array input correctly and returns one result per row. Also check that your output column is empty - if any cell below the ARRAYFORMULA cell contains data, Sheets will truncate the output at that row.

What's the difference between ARRAYFORMULA and just using a function that auto-expands?

As of 2024, most Google Sheets functions are array-native and spill results automatically without ARRAYFORMULA. `=IF(A2:A100>0, 1, 0)` spills without a wrapper. ARRAYFORMULA is still needed when you're forcing a non-array function like SUMIF to evaluate row-by-row across a range, or when you want explicit control over whether and how results expand. For a financial model, you'll still reach for ARRAYFORMULA most often with SUMIFS-based row-level aggregations where you need one result per cost center or time period.

Does ARRAYFORMULA slow down my Google Sheets model?

Yes, if used carelessly. An ARRAYFORMULA referencing a full column like `A:A` on a 50,000-row sheet will recalculate entirely every time any cell in column A changes. On a model with 8 interconnected tabs, this compounds. The fix is explicit range bounds: use `A2:A5000` instead of `A:A` if your data won't exceed 5,000 rows. Google Sheets has a hard cap of 10 million cells per spreadsheet, and large ARRAYFORMULA ranges count against both that limit and your recalculation budget.

Can I use ARRAYFORMULA across multiple tabs in a financial model?

Yes, and this is where it becomes most useful. A formula like `=ARRAYFORMULA(SUMIFS('P&L'!$D:$D, 'P&L'!$B:$B, Assumptions!$A$3:$A$14))` sits on your Summary tab and pulls one aggregated value per row from the P&L tab, dynamically, without copying. The key rule: the range you're iterating over (in this case `Assumptions!$A$3:$A$14`) must be a column or row of the same size as the corresponding lookup key range in your source tab, or Sheets will throw a size mismatch error.

Google Sheets ARRAYFORMULA: Beyond the Help Docs

Q: What does ARRAYFORMULA actually do in Google Sheets?

ARRAYFORMULA forces a formula to evaluate across a range of cells and output multiple results from a single cell. According to the [Google Docs Editors Help documentation](https://support.google.com/docs/answer/3093275), it "enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays." In practice, it means writing `=ARRAYFORMULA(B2:B500 * C2:C500)` once rather than copying `=B2*C2` down 499 rows - and having one formula to maintain instead of 499.

The documentation describes ARRAYFORMULA as a function that "enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays." Correct. But it doesn't tell you which functions need the wrapper, which ones silently fail inside it, or how to wire it across tabs without wrecking your calculation chain.

This article covers the gap.

What the Official Docs Actually Tell You

The help page covers 3 things well:

The keyboard shortcut (Ctrl+Shift+Enter on Windows, Cmd+Shift+Enter on Mac) wraps any formula in ARRAYFORMULA automatically
It works with arithmetic operators, so =ARRAYFORMULA(A2:A100 * B2:B100) multiplies two columns element-by-element
Nested array-aware functions like IF, LEN, and basic math operators don't need the wrapper in modern Sheets (as of 2024, most array functions expanded automatically)

That third point is important and the docs understate it. Google has quietly made many functions array-native. =IF(A2:A100>0, "positive", "negative") spills without ARRAYFORMULA in current Sheets. The wrapper is now most useful when you're forcing a non-array function to process a range, or when you need explicit control over output shape.

Where ARRAYFORMULA Actually Earns Its Place in FP&A Work

The honest use case for ARRAYFORMULA in a multi-tab model isn't column-multiplication. It's conditional aggregation and cross-tab lookup that you want to maintain from a single cell, not copied down 200 rows.

Pattern 1: SUMIF Across a Full Column Range

The official help page doesn't mention this, but SUMIF and SUMIFS don't return arrays natively when the sum range is a column. ARRAYFORMULA forces the row-by-row evaluation.

=ARRAYFORMULA(
  SUMIFS(
    'P&L'!$D:$D,          -- sum: revenue by line
    'P&L'!$B:$B, Assumptions!$B$3:$B$14,  -- match: cost center code
    'P&L'!$C:$C, ">=" & Assumptions!$D$3  -- filter: period >= start
  )
)

This sits in a single cell on your Summary tab and returns 12 values, one per month in Assumptions!$B$3:$B$14. No copy-paste, no formula drift between rows, no chance that row 9 references a different period than row 10.

Pattern 2: Multi-Condition Text Classification

Tagging 500 GL line items by whether they're COGS, OpEx, or below-the-line is tedious to maintain with IF chains copied down a column. One ARRAYFORMULA cell handles it:

=ARRAYFORMULA(
  IF('GL Detail'!C2:C="Cost of Revenue", "COGS",
  IF('GL Detail'!C2:C="Sales & Marketing", "S&M",
  IF('GL Detail'!C2:C="General & Administrative", "G&A",
  "Other")))
)

When finance adds a new GL category and the classification breaks for 30 rows, it breaks in exactly one place. That's the difference between a 2-minute fix and a 20-minute audit.

Pattern 3: Dynamic Row Count from Another Tab

The help docs don't address this scenario at all. If your revenue tab has a variable number of SKUs that changes quarterly, hardcoding A2:A100 creates either dead rows or truncated data. The workaround:

=ARRAYFORMULA(
  IF(
    'Revenue'!A2:A = "",  -- stops where data ends
    "",
    'Revenue'!B2:B * Assumptions!$C$4  -- price * volume adjustment
  )
)

The IF(...="", "") guard is the pattern the docs never show. Without it, ARRAYFORMULA fills every empty row in the column with a result, which kills performance on a sheet with 10,000 potential rows.

What ARRAYFORMULA Won't Do (That You'll Try Anyway)

Google's help page lists no limitations. There are several.

VLOOKUP doesn't work inside ARRAYFORMULA the way you'd expect. If you write =ARRAYFORMULA(VLOOKUP(A2:A100, 'Rates'!$A:$B, 2, 0)), it will often return only the first match or error. Use INDEX/MATCH instead:

=ARRAYFORMULA(
  IFERROR(
    INDEX('Rates'!$B:$B,
      MATCH('Revenue'!A2:A100, 'Rates'!$A:$A, 0)
    ),
    0
  )
)

QUERY and IMPORTRANGE don't need and don't benefit from ARRAYFORMULA. Wrapping them adds overhead without changing output. The official docs don't warn you off this.

Mixed references break silently. If your ARRAYFORMULA references a single cell like $D$4 alongside a range like C2:C, Sheets will broadcast the single cell correctly. But if you accidentally write D4 (relative), Sheets shifts it per-row during evaluation, and the results look plausible while being wrong. This is the model-killer. Audit with Ctrl+~ (show formulas mode) if numbers look off.

Performance Reality for Large Models

Google Sheets caps at 10 million cells. The help docs mention this limit in passing. What they don't mention: a single ARRAYFORMULA operating on a full column (A:A) on a tab with 50,000 rows, called from 8 other tabs, can push recalculation times past 30 seconds.

The fix is range-bounding. If your transaction data never exceeds row 5,000, write A2:A5000, not A:A. For quarterly board pack models where the P&L tab feeds 6 downstream tabs, this difference is the gap between a model that opens in 4 seconds and one that times out.

As of June 2026, Google Sheets recalculates all dependent ARRAYFORMULA results on any edit to the source range. There's no way to make them static short of copy-pasting values - which defeats the purpose.

The Keyboard Shortcut the Docs Mention (and One They Don't)

Ctrl+Shift+Enter wraps your current formula in ARRAYFORMULA automatically. That's in the docs.

What's not: if you delete the { curly braces that sometimes appear around legacy array formulas (carried over from Excel), Sheets may re-evaluate differently than you expect. The curly-brace syntax is Excel's array formula notation. Google Sheets uses ARRAYFORMULA as the wrapper instead. If you've imported a model from Excel, check any formula that looks like {=SUM(IF(...))} - it needs to be rewritten as =ARRAYFORMULA(SUM(IF(...))) or it may not evaluate correctly in Sheets.

When to Skip ARRAYFORMULA Entirely

If you're doing the same calculation 3 times in a column and the model won't grow, copying a formula down is faster to build and easier for another analyst to audit. ARRAYFORMULA adds a layer of indirection that slows down model review when someone else is stepping through your logic.

The sweet spot is 20+ rows, variable length, or anywhere the same formula will need to stay synchronized across a growing dataset. For a fixed 5-row assumptions table, it's overkill.

ModelMonkey can scan a multi-tab model and flag where ARRAYFORMULA would consolidate scattered copied formulas, including cases where the same logic appears in 40 rows but references a single lookup tab. It's the kind of cleanup that takes an hour manually and a few seconds with an AI that can read your formula structure. Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.