← Back to Error Directory

ARRAYFORMULA Errors in Google Sheets — Complete Fix

Google Sheets' answer to dragging a formula down a thousand rows — here's when it breaks and why.

Table of Contents

1. The Problem ARRAYFORMULA Solves

Many Google Sheets functions, especially simple arithmetic and basic IF logic, are designed by default to process exactly one cell and return exactly one result. Without ARRAYFORMULA, applying such a formula to a range would only calculate for the single top row, requiring you to manually copy it down every subsequent row. ARRAYFORMULA forces the enclosed formula to process an entire range at once, spilling results down automatically — similar in spirit to Excel's native dynamic arrays, but requiring this explicit wrapper function rather than happening automatically.

2. Interactive Demo: Array Size Matching

See exactly why ARRAYFORMULA requires every referenced range to share the same dimensions.

3. Cause 1: Mismatched Array Sizes

When ARRAYFORMULA processes an operation involving two or more ranges (like multiplying quantity by price across many rows), every range involved must share the exact same number of rows. A mismatch — one range spanning 100 rows, another spanning only 50 — produces an explicit error, since ARRAYFORMULA has no sensible way to pair up rows that don't correspond one-to-one.

4. Cause 2: Functions That Don't Support Array Mode

Not every Google Sheets function is designed to operate on multiple values simultaneously, even when wrapped in ARRAYFORMULA. Certain functions inherently expect and process only a single input value, and wrapping them in ARRAYFORMULA either has no effect beyond the first row, or produces an unexpected result rather than the row-by-row processing you might expect from functions that do genuinely support array operation.

5. Cause 3: Wrapping a Formula That Doesn't Need It

Many modern Sheets functions — QUERY, FILTER, SORT, UNIQUE — already return arrays natively and spill their own results automatically, without requiring an ARRAYFORMULA wrapper at all. Adding ARRAYFORMULA around a function that's already array-native is unnecessary and, in occasional edge cases, can produce a subtly different (and sometimes unexpected) result compared to using the function on its own.

6. Cause 4: Nesting ARRAYFORMULA Unnecessarily

Wrapping multiple nested ARRAYFORMULA calls within a single larger formula, when only the outermost wrapper is actually needed, can create confusing, hard-to-debug behavior. Generally, a single ARRAYFORMULA wrapped around the entire outer formula is sufficient to force array-style processing throughout the whole calculation chain — nested inner ARRAYFORMULA calls are rarely necessary and add unnecessary complexity to troubleshoot later.

7. How This Relates to Excel's Dynamic Arrays

Excel's modern dynamic array engine (365, Excel 2021+) achieves conceptually similar row-by-row spilling behavior automatically, without needing an equivalent wrapper function — this is precisely why ARRAYFORMULA() is listed as a Sheets-exclusive function in our cross-platform formula compatibility guide. When translating a Sheets formula using ARRAYFORMULA into modern Excel, the wrapper itself is typically unnecessary and can simply be omitted, letting Excel's native dynamic array behavior handle the row-by-row spilling automatically.

8. Frequently Asked Questions

When do I actually need ARRAYFORMULA in Google Sheets?

ARRAYFORMULA is needed when you want a formula that naturally operates on a single cell, such as basic arithmetic or a simple IF statement, to instead process an entire range and spill results down multiple rows without manually copying the formula to each individual row.

Why does ARRAYFORMULA return a mismatched array sizes error?

This happens when the formula inside ARRAYFORMULA references multiple ranges of different sizes, such as one range covering 100 rows and another covering only 50, since ARRAYFORMULA requires every array-processed component to align to the same dimensions.

Do all Google Sheets functions work inside ARRAYFORMULA?

No, certain functions are inherently designed to process only a single value at a time and do not support array-style operation even when wrapped in ARRAYFORMULA, which can produce unexpected results or errors rather than the intended row-by-row array processing.

Related Guides

Editorial Disclaimer: This guide is developed to the best of our domain knowledge and tested against Excel 2019+, Microsoft 365, and Google Sheets. Content is updated continuously as spreadsheet calculation engines evolve.