How does ARRAYFORMULA work in Google Sheets?

ARRAYFORMULA tells Google Sheets to evaluate a formula against an entire range rather than a single cell. Put `=ARRAYFORMULA(C2:C * D2:D)` in one cell and it returns a result for every row in columns C and D simultaneously. It also enables functions like VLOOKUP, LEFT, and IFERROR to process entire columns - functions that otherwise only handle one cell at a time. According to Google's official documentation, it "enables the display of values returned from an array formula into multiple rows and/or columns."

Can ARRAYFORMULA replace VLOOKUP across an entire column?

Yes, and it's one of the most common use cases in FP&A. `=ARRAYFORMULA(IFERROR(VLOOKUP('P&L'!B2:B, 'Chart of Accounts'!$A:$C, 3, FALSE), "Unmapped"))` maps an entire column against a reference table in one formula. This handles 600 GL code lookups the same way it handles 6 - and unlike 600 copied VLOOKUPs, it can't get out of sync when rows are inserted.

Why does my ARRAYFORMULA return a #VALUE! error?

Usually the inner function doesn't support array inputs natively, or you've mixed a single-cell reference with a range reference in a way that doesn't broadcast. Check whether any argument inside your formula is a fixed single cell when it should be a range. Also confirm you're not wrapping QUERY, SORT, or FILTER - those handle arrays independently and break when ARRAYFORMULA is bolted on top.

Does ARRAYFORMULA slow down Google Sheets?

It can. Formulas that reference entire columns (A:A instead of A2:A1000) count every cell toward Google's 10 million cell limit per spreadsheet, and large cross-tab SUMIFS arrays recalculate on every edit. For models with 10,000+ rows and multiple SUMIFS-based ARRAYFORMULAs, benchmark both approaches: helper columns often recalculate faster because Sheets only recalculates changed cells rather than the full array.

What's the difference between ARRAYFORMULA and BYROW?

ARRAYFORMULA broadcasts a formula across a range - applying the same operation to every element at once. BYROW, added to Sheets in late 2022, applies a LAMBDA function to each row individually, which is cleaner when the logic is too complex to express as a single broadcasting formula. For standard FP&A tasks (arithmetic, VLOOKUP, conditional flags), ARRAYFORMULA is faster to write. For multi-step per-row logic that doesn't broadcast cleanly, BYROW is the better call.

ARRAYFORMULA in Google Sheets: FP&A Reference (2026)

This covers the two operating modes, the SUMIF pattern that catches analysts off-guard, where the function breaks, and a direct comparison with BYROW (released late 2022, now the better tool for certain patterns as of July 2026).

The Two ARRAYFORMULA Modes You Need

Most analysts discover ARRAYFORMULA through one mode and never fully figure out the other. They're different enough to treat separately.

Mode 1: Arithmetic across columns. When your formula is just math, the wrapper is minimal. Take a revenue model where column C is units and column D is ASP:

=ARRAYFORMULA(C2:C * D2:D)

That produces revenue for every row. Add a row to your data, the formula extends automatically. This mode works with any operator: +, -, *, /, ^. It handles comparisons too, so you can flag rows conditionally:

=ARRAYFORMULA(IF('P&L'!E2:E >= Assumptions!$B$3, "Above Target", "Below Target"))

The ARRAYFORMULA(IF) guide covers the full conditional pattern in detail.

Mode 2: Wrapping non-native functions. VLOOKUP, LEFT, MID, FIND, and IFERROR don't process arrays natively. ARRAYFORMULA forces them to run down a column:

=ARRAYFORMULA(IFERROR(VLOOKUP('P&L'!B2:B, 'Chart of Accounts'!$A:$C, 3, FALSE), "Unmapped"))

This maps 600 GL line items against a chart of accounts in one formula. Without ARRAYFORMULA, you're copying that VLOOKUP 600 times, and hoping nobody inserts a row in the wrong place.

According to Google's official Sheets documentation, ARRAYFORMULA "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." The operative word is "enables" - it doesn't change what the inner function does, it just tells Sheets to apply it across a range rather than a single cell.

ARRAYFORMULA with SUMIF: The Pattern That Surprises People

SUMIF and SUMIFS behave differently inside ARRAYFORMULA depending on whether the criteria is a fixed value or an array.

The pattern most analysts write first:

=ARRAYFORMULA(SUMIF('P&L'!$B:$B, D2:D, 'P&L'!$C:$C))

This works when D2:D contains the criteria you want to match. The output is one sum per row - SUMIF evaluates against each element in D2:D separately. That's the multi-department rollup pattern: one formula, one result per department.

Where people get tripped up: combining >= criteria with a column-level reference in SUMIFS.

=ARRAYFORMULA(SUMIFS('P&L'!C:C, 'P&L'!B:B, ">=" & Assumptions!$B$3, 'P&L'!A:A, E2:E))

This runs a conditional sum for each entity in E2:E where the date is at or after the start date in your Assumptions tab. It works - but recalculation slows noticeably on large datasets. In practice, this pattern runs 8-12 seconds on a model with 10,000 rows versus under 2 seconds for an equivalent helper-column approach.

At that scale, consider whether a pivot-style aggregation tab is cleaner than pushing ARRAYFORMULA through a large SUMIFS.

Where ARRAYFORMULA Breaks

Some functions don't cooperate with ARRAYFORMULA regardless of how you structure the formula. Knowing this list saves at least 30 minutes of debugging.

QUERY ignores ARRAYFORMULA entirely. The array wrapper does nothing useful here. Use separate QUERY calls or restructure the data source. The Excel QUERY function guide covers this pattern if you're working across environments.

SORT, UNIQUE, and FILTER don't need it. These are native array functions already - they return arrays without ARRAYFORMULA. Wrapping them adds nothing and occasionally causes range conflicts.

Functions that return a single value won't expand. =ARRAYFORMULA(SUM(A:A)) returns one number, not a cumulative sum column. For running totals, use SCAN (added to Sheets in the November 2022 LAMBDA helper functions rollout) or a helper column.

The 10 million cell ceiling. Google Sheets caps total cells per spreadsheet at 10 million, and ARRAYFORMULA formulas that reference entire columns (A:A rather than A2:A1000) count every cell in that column toward the limit. Google's Sheets documentation flags this directly: "Very large array formulas may slow your spreadsheet." In a model with 15 full-column ARRAYFORMULAs, you can consume 15% of that budget before adding any real data.

ARRAYFORMULA vs. BYROW in 2026

Google added BYROW and BYCOL to Sheets in the same late 2022 LAMBDA functions release, and by 2026 they've replaced ARRAYFORMULA for row-level calculations that need custom logic.

The practical difference: ARRAYFORMULA broadcasts a single formula across a range. BYROW applies a LAMBDA function to each row individually. That sounds similar but the behavior splits when your formula involves multiple conditions or lookups that don't broadcast cleanly.

Scenario	Better Choice
Column arithmetic (revenue = units × price)	ARRAYFORMULA
VLOOKUP / IFERROR across a column	ARRAYFORMULA
Conditional logic with IF	ARRAYFORMULA(IF(...))
Cross-tab SUMIFS aggregation	ARRAYFORMULA(SUMIFS(...))
Complex per-row logic with custom steps	BYROW + LAMBDA
Running total / cumulative sum	SCAN

BYROW is cleaner when the logic is complex enough that it reads as a function. ARRAYFORMULA is faster to write for the 80% of FP&A tasks that are just broadcasting math down a column.

ModelMonkey can generate ARRAYFORMULA and BYROW formulas from a plain description - useful when you're wiring up a 12-tab model and don't want to debug broadcasting behavior by hand.