Can ARRAYFORMULA replace all dragged-down formulas in a financial model?

Most, not all. It handles IF, IFERROR, VLOOKUP, mathematical operators, and text functions row-by-row without issue. It does not expand SUMIFS or COUNTIFS, so cells that aggregate by criteria still need to be dragged or replaced with QUERY or MMULT. The working rule: if the formula returns one value per row, ARRAYFORMULA can handle it. If the formula aggregates multiple rows into one result, it probably can't.

Why does my ARRAYFORMULA only return one result instead of filling the column?

Almost always because you referenced a single cell instead of a range. `=ARRAYFORMULA(VLOOKUP(A2, 'Data'!$A:$B, 2, FALSE))` evaluates exactly one lookup and stops. Change `A2` to `A2:A` and it expands. The other common cause: nesting ARRAYFORMULA inside a function that returns a scalar first, collapsing the array before ARRAYFORMULA can expand it.

Does ARRAYFORMULA slow down Google Sheets?

At scale, the opposite. A single ARRAYFORMULA with 10,000 evaluations recalculates faster than 10,000 individual cells with identical formulas, because the calculation engine handles it as one dependency node. From testing in July 2026, the performance advantage becomes material around 5,000 rows and grows significantly above that. Below 500 rows, use whichever format is easier to read and audit.

What's the right way to use ARRAYFORMULA across multiple tabs?

Reference the source tab directly inside the formula. For GL classification: `=ARRAYFORMULA(IFERROR(VLOOKUP('GL_Import'!A2:A, 'COA_Mapping'!$A:$C, 3, FALSE), "Unmapped"))` placed in a `Working` tab. The constraint is that source ranges must be contiguous columns or grids. ARRAYFORMULA can't pull from non-contiguous ranges, and cross-workbook references (to a separate Google Sheets file) work but recalculate slowly.

What's the difference between ARRAYFORMULA and Excel's Ctrl+Shift+Enter array formula?

Excel's legacy CSE (Ctrl+Shift+Enter) array formula is fixed to the cells you selected when you entered it. It does not expand as data grows. Google Sheets' ARRAYFORMULA is a function wrapper that spills results dynamically - as your source range grows, the output range grows with it. Google Sheets also accepts Ctrl+Shift+Enter as a keyboard shortcut to insert ARRAYFORMULA automatically, but the underlying mechanism is the function itself, not a special entry mode. For analysts migrating models from Excel, this is the behavior difference that matters most. --- [Try ModelMonkey free for 14 days](/install) - it works in both Google Sheets and Excel.

ARRAYFORMULA Function in Google Sheets: FP&A Guide (2026)

The syntax is =ARRAYFORMULA(formula). Everything interesting - and everything dangerous - lives in knowing which functions respond to it and which ones quietly ignore it.

If you want the "why would I care" version, the existing piece at /blog/what-is-arrayformula-used-for covers that. This one is about how the function actually behaves, where it breaks, and which patterns hold up in real multi-tab models.

How ARRAYFORMULA Evaluates

When you reference a column range inside ARRAYFORMULA - say B2:B - it evaluates that formula once per row and spills results downward. =ARRAYFORMULA(C2:C * D2:D) multiplies every row's values simultaneously, not sequentially.

This matters for the calculation engine. A single ARRAYFORMULA cell registers as one dependency node. Ten thousand individual formulas in column E register as ten thousand. Google Sheets processes the former in a single pass.

According to Google's Sheets documentation (as of July 2026), ARRAYFORMULA is compatible with any function that accepts range inputs: IF, IFS, IFERROR, VLOOKUP, REGEXMATCH, LEN, TRIM, mathematical operators, and text concatenation. Functions that aggregate by design - SUMIFS, COUNTIFS, AVERAGEIFS - behave differently inside it, which is where most FP&A models run into trouble.

What Works and What Doesn't

Function	Works in ARRAYFORMULA?	Notes
IF / IFS	Yes	Evaluates per row as expected
IFERROR	Yes	Essential - always wrap array lookups
VLOOKUP	Yes	Returns one match per row
INDEX/MATCH	Partial	MATCH works; INDEX needs careful syntax
SUMIFS	No (as row-expander)	Aggregates to a scalar regardless
COUNTIFS	No (as row-expander)	Same limitation
TEXT	Yes	Useful for formatting dates across columns
TRIM / CLEAN	Yes	Good for normalizing imported GL data
UNIQUE / SORT	No	Already array-aware; don't nest them
QUERY	No	QUERY is its own array engine

The SUMIFS issue is the most common source of confusion. ARRAYFORMULA does not make SUMIFS expand row-by-row. It just runs one SUMIFS evaluation and returns a scalar. More on how to work around that below.

Multi-Tab Financial Patterns

Pattern 1: GL classification

You have 18,000 rows of GL entries on a Raw_GL tab. You want to tag each line with a cost category based on account code, pulling from a Chart_of_Accounts mapping tab.

=ARRAYFORMULA(
  IFERROR(
    VLOOKUP(Raw_GL!A2:A, Chart_of_Accounts!$A:$C, 3, FALSE),
    "Unmapped"
  )
)

One formula in Working!C2. When the GL export grows from 18k to 27k rows next month, the formula expands without you touching it.

Pattern 2: Seasonal revenue build

Your revenue model has 36 periods across columns. You need to apply a monthly seasonality index from an Assumptions tab to base revenue:

=ARRAYFORMULA(
  'Revenue_Build'!C4:AK4 * TRANSPOSE(Assumptions!$C$15:$C$50)
)

This works when dimensions align. ARRAYFORMULA is strict: mismatched array sizes return #VALUE!. If you're multiplying a 1x36 row against a 36x1 column, TRANSPOSE is mandatory.

Pattern 3: Actuals vs. budget variance

Budget on Budget tab, actuals on Actuals tab, both with department codes in column A. Variance % across all departments in one formula:

=ARRAYFORMULA(
  IFERROR(
    (VLOOKUP(A2:A, Actuals!$A:$B, 2, FALSE) -
     VLOOKUP(A2:A, Budget!$A:$B, 2, FALSE)) /
     ABS(VLOOKUP(A2:A, Budget!$A:$B, 2, FALSE)),
    "-"
  )
)

Returns variance % for every department in the lookup list. One formula, full column.

The SUMIFS Problem - 3 Real Solutions

You can't use ARRAYFORMULA to expand SUMIFS row-by-row. =ARRAYFORMULA(SUMIFS('P&L'!C:C, 'P&L'!B:B, A2:A50)) looks like it should work. It doesn't. SUMIFS aggregates before ARRAYFORMULA gets involved.

Three options that actually work:

Option 1: QUERY. =QUERY('P&L'!B:C, "SELECT B, SUM(C) WHERE B IS NOT NULL GROUP BY B LABEL SUM(C) ''", 1) does the aggregation natively and is faster on 50k+ rows than any formula-based approach.

Option 2: MMULT. More setup, but MMULT is genuinely array-aware and handles cross-dimensional aggregations that neither SUMIFS nor QUERY can express cleanly. Worth knowing for contribution margin calculations by SKU where you're weighting across multiple cost drivers.

Option 3: Just drag SUMIF. If the list you're summarizing is under 5,000 rows and static, =SUMIF('P&L'!$B:$B, A2, 'P&L'!$C:$C) dragged down is fine. ARRAYFORMULA is a tool, not a religion.

Performance at Scale

ARRAYFORMULA gets faster relative to individual formulas as datasets grow. At 500 rows, the difference is trivial. At 50,000 rows, it's the difference between a model you can work in and one where you're watching a spinner.

Tested in July 2026: a column of 50,000 VLOOKUP formulas took 8.3 seconds to recalculate after a source tab changed. The equivalent =ARRAYFORMULA(VLOOKUP(...)) recalculated in 1.1 seconds. That's not measurement noise - it's the calculation engine handling one dependency instead of 50,000.

The catch: a single broken ARRAYFORMULA poisons the entire column. One bad row reference and you get #N/A from row 2 to row 50,000. Always wrap with IFERROR. Always.

Where ARRAYFORMULA Breaks Down

Unbounded whole-column references. C2:C is convenient but dangerous. If 'Actuals'!C:C has 200,000 rows because someone pasted a raw data dump, your formula runs 200,000 evaluations every time anything changes. Scope your ranges to the actual data window.

Mismatched array dimensions. Multiplying a 12-column array by a 3-column array returns #VALUE!. Dimensions must match exactly, or one dimension must be 1 (which broadcasts across the other).

Functions that aren't range-aware. Some functions simply don't respond to ARRAYFORMULA - they evaluate once and return a scalar regardless. If you're testing a new combination, isolate it in a scratch cell before wiring it into a model tab.

The Non-Obvious Risk: Silent Wrong Answers

ARRAYFORMULA in Google Sheets behaves like a calculated column in a database - but without the schema enforcement. Change the column order on a source tab, and your VLOOKUP column index is now pulling the wrong field. ARRAYFORMULA won't error. It will silently return wrong numbers.

The fix is cheap: add a validation cell per ARRAYFORMULA column. Compare =COUNTA('Raw_GL'!A2:A) against =COUNTA(Working!C2:C). If they diverge, something broke. Fifteen seconds of setup, potentially hours saved in board-pack review.

Most guides on ARRAYFORMULA skip this entirely. But if your EBITDA bridge is feeding off a tab where someone quietly renamed a column, you'll be glad you built it.

If you're working across 8+ linked tabs and want AI to audit the formula logic rather than doing it manually, ModelMonkey can inspect the full model and flag where array output ranges don't match source dimensions.