Can IFS work inside ARRAYFORMULA in Google Sheets?

No - as of July 2026, IFS does not expand array-style inside ARRAYFORMULA. It evaluates only the first row and returns a single value, not a column. Use nested IF statements for multi-tier logic up to about 4 tiers, or build a reference table and use `ARRAYFORMULA(VLOOKUP())` for cleaner logic when you have 5+ tiers.

How do I handle #DIV/0! errors inside an ARRAYFORMULA(IF)?

Wrap the specific operation that can fail with IFERROR inside the ARRAYFORMULA: `=ARRAYFORMULA(IF(A2:A="","",IFERROR(B2:B/C2:C,0)))`. Don't wrap the entire ARRAYFORMULA with IFERROR - that suppresses all errors including logic bugs you want to catch during model build. Target only the operation that produces the error.

Can a single ARRAYFORMULA(IF) write results to multiple columns?

No. One ARRAYFORMULA returns one output column. If your conditional logic needs to populate Revenue, COGS, and Gross Margin as separate columns, write 3 separate ARRAYFORMULA formulas - one per column. This is a hard structural limit, not something a workaround can solve within a single formula.

Does ARRAYFORMULA(IF) slow down large Google Sheets models?

It can, specifically when you chain ARRAYFORMULA outputs together - where the result of one array formula feeds directly into another. In a 36-month model with 200 SKUs, each array column touches roughly 7,200 cells, and chained dependencies multiply that recalculation cost on every edit. Fix it by evaluating intermediate results in a helper column and crystallizing closed-period data with Paste Special → Values so static months don't recalculate.

ARRAYFORMULA(IF) in Google Sheets: Finance Guide

Q: Why does my ARRAYFORMULA(IF) fill down hundreds of empty rows?

You're missing the blank-row guard. Add an outer IF that short-circuits on empty cells: `=ARRAYFORMULA(IF(A2:A="","",your_logic_here))`. Without it, ARRAYFORMULA evaluates every cell in the column range including the rows below your data, flooding the sheet with labels that break any SUMIFS or COUNTIFS referencing that column.

The syntax wraps directly: =ARRAYFORMULA(IF(logical_array, value_if_true, value_if_false)). Each argument expands automatically across however many rows your data occupies. That single behavioral difference - auto-expansion - is what makes it worth understanding thoroughly before you build anything with more than 2 tabs.

The Basic ARRAYFORMULA(IF) Pattern

Start with the blank-row guard. Without it, ARRAYFORMULA evaluates every cell in the column down to row 50,000 in some Sheets configurations, and you get 50,000 labels flooding your column. That breaks every SUMIFS, COUNTIFS, and pivot that references it downstream.

=ARRAYFORMULA(
  IF('P&L'!B2:B="", "",
    IF('P&L'!B2:B="Revenue", 'P&L'!C2:C * Assumptions!$B$3, 0)
  )
)

The outer IF guards blanks. The inner IF does the actual logic. That two-layer pattern is the foundation of nearly every ARRAYFORMULA(IF) you'll write in a financial model.

Here's a more realistic example - a SKU-level contribution margin model flagging products where gross margin has compressed below the board-approved floor of 38.5%:

=ARRAYFORMULA(
  IF('P&L'!A2:A="", "",
    IF(('P&L'!C2:C - 'P&L'!D2:D) / 'P&L'!C2:C < Assumptions!$B$7,
      "Below Floor", "OK"
    )
  )
)

Where Assumptions!$B$7 holds the 38.5% threshold. SKUs flagged "Below Floor" feed into a COUNTIFS for the board pack margin bridge. The reference stays live - adjust the threshold in Assumptions and the flags repopulate instantly.

Nesting Logic Without Losing Your Mind

Google Sheets caps nested IF at 64 levels, though readability collapses well before that. For tiered structures - commission buckets, territory classifications, draw-down tranches - you stack IF inside IF:

=ARRAYFORMULA(
  IF('Sales'!C2:C="","",
    IF('Sales'!C2:C >= Assumptions!$B$3, "Tier 1",
      IF('Sales'!C2:C >= Assumptions!$B$4, "Tier 2",
        IF('Sales'!C2:C >= Assumptions!$B$5, "Tier 3",
          "Below Minimum"
        )
      )
    )
  )
)

Where $B$3 = $2.1M, $B$4 = $850K, $B$5 = $250K are quota thresholds living in Assumptions so the CFO can adjust them without touching formula logic.

IFS would be cleaner syntax here - one function, multiple condition-result pairs. But IFS does not expand inside ARRAYFORMULA as of July 2026. Neither does SWITCH. If you need multi-tier logic beyond 4 levels, your practical options are nested IF or a lookup against a reference table using ARRAYFORMULA(VLOOKUP()) or ARRAYFORMULA(INDEX(MATCH())). The lookup approach scales better for 8+ tiers and keeps the Assumptions tab as the single source of truth.

Handling Errors Inside ARRAYFORMULA(IF)

Division, lookups, and text parsing all throw errors when the denominator is zero, the lookup target doesn't exist, or the source cell is blank. Inside an ARRAYFORMULA, one bad row propagates the error visually across the entire column. Wrap the specific failing operation with IFERROR:

=ARRAYFORMULA(
  IF('P&L'!B2:B="", "",
    IFERROR(
      ('P&L'!C2:C - 'P&L'!D2:D) / 'P&L'!C2:C,
      0
    )
  )
)

Position matters. IFERROR goes inside the ARRAYFORMULA, not around it. Wrapping the entire ARRAYFORMULA with IFERROR converts all errors to 0 - including formula logic bugs you actually want to see during model build. Wrap only the specific operation that can fail.

For margin calculations: a revenue figure of $0 in months before a product launched is a common divide-by-zero source. IFERROR(expr, 0) handles it cleanly, but think about whether 0% margin is meaningfully different from "no data" for your output. In a 36-month model with 200 SKUs, that distinction shows up in trailing averages and cohort analysis.

What ARRAYFORMULA(IF) Won't Do

A few things break inside ARRAYFORMULA that you'd expect to work:

IFS and SWITCH don't expand array-style. The formula evaluates only the first row and returns a scalar, not a column.

VLOOKUP with approximate match (range_lookup = TRUE) behaves unpredictably in array context. Use INDEX(MATCH()) or VLOOKUP with exact match.

Multi-column outputs from a single IF branch - ARRAYFORMULA returns one column per formula. If your conditional logic needs to populate separate Revenue, COGS, and Gross Margin columns, that's 3 formulas.

Volatile functions inside IF branches - RAND(), NOW(), TODAY() inside ARRAYFORMULA recalculate on every sheet change. In a model with 7,200 cells tied to array formulas, that's a noticeable lag.

Performance at Scale

A 36-month P&L model with 200 SKUs means roughly 7,200 cells per formula column. That's fine in isolation. Where recalculation slows is when you chain multiple ARRAYFORMULA outputs together - formula A feeds formula B feeds formula C. Chained arrays multiply recalculation cost.

Two practical fixes: evaluate once in a helper column and reference that downstream, and crystallize closed periods with Paste Special → Values when the month closes and the logic won't change.

The iteration loop on ARRAYFORMULA(IF) - write, check for blank-row bleed, debug errors, adjust logic, repeat - is where mid-quarter time gets eaten. Try ModelMonkey free for 14 days - it works in both Google Sheets and Excel.