Fix the ARRAYFORMULA error

Excel & Google Sheets

What ARRAYFORMULA means

ARRAYFORMULA tells Google Sheets to evaluate a formula across an entire range rather than a single cell, writing results down the column automatically. When it produces only one result, returns an error, or expands into the wrong cells, the inner formula is usually not array-compatible, the ranges are different sizes, or there is an output collision with existing data.

Common causes

The inner function does not support array evaluation natively — for example, TRIM(A2:A100) works with ARRAYFORMULA but functions like VLOOKUP need specific syntax to behave as arrays.
Range sizes are mismatched inside the formula — multiplying A2:A100 (99 rows) by B2:B50 (49 rows) produces an error because the arrays cannot broadcast.
The ARRAYFORMULA output cells are occupied by data or other formulas, so Sheets truncates expansion or shows a collision error.
The formula uses IF with a scalar condition instead of an element-wise comparison: IF(A2:A100="Yes",1,0) needs ARRAYFORMULA to expand, but IF(COUNTA(A2:A100)>0,1,0) is always scalar.
The inner formula already contains an ARRAYFORMULA (double-wrapping), which causes unpredictable behavior.

Example fix

Broken

=ARRAYFORMULA(VLOOKUP(A2:A100,D2:E50,2,0))

Fixed

=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A100,D2:E50,2,FALSE),""))

Added IFERROR to handle any unmatched rows cleanly (instead of returning #N/A for the whole array), and changed 0 to FALSE for clarity. The array VLOOKUP itself was already correct.

Try it with your data

Edit the grid or formula, then run it through a real spreadsheet engine — no signup.

Sample data — click any cell to edit

Cell row 1 column 1	Cell row 1 column 2
Cell row 2 column 1	Cell row 2 column 2
Cell row 3 column 1	Cell row 3 column 2
Cell row 4 column 1	Cell row 4 column 2

Formula

Runs server-side · free · no signup

How to fix it

1Verify the inner function is array-compatible. Most arithmetic and text functions are; aggregations like SUM inside ARRAYFORMULA collapse to one value and should not be used there.
2Use consistent range sizes or open-ended column references (A2:A) to ensure arrays broadcast correctly without size mismatches.
3Clear the cells below the ARRAYFORMULA cell so the expansion zone is empty.
4Remove double-wrapping: if a function like UNIQUE or FILTER already returns an array natively in Sheets, do not wrap it in ARRAYFORMULA.
5To apply VLOOKUP as an array: =ARRAYFORMULA(IFERROR(VLOOKUP(A2:A100,LookupTable,2,0),"")) — this applies VLOOKUP to every value in A2:A100.

Stop hunting errors by hand.

Upload your spreadsheet and the Auditor flags every ARRAYFORMULA and broken formula at once — or paste this one formula and get the fix explained.

Frequently asked

Should I use ARRAYFORMULA or just type the formula in each cell manually?

ARRAYFORMULA is better for large ranges — it is faster, updates automatically when rows are added, and keeps the sheet clean with a single formula. Use it whenever you would otherwise copy a formula down an entire column.

Why does ARRAYFORMULA with SUM return only one number instead of a sum per row?

SUM collapses its argument to a single total. For per-row summation across columns, use arithmetic: =ARRAYFORMULA(B2:B+C2:C+D2:D) instead of SUM inside ARRAYFORMULA.

Does ARRAYFORMULA work the same in Excel?

Excel does not have ARRAYFORMULA. In Excel 365, formulas entered normally are array-aware. In older Excel, press Ctrl+Shift+Enter to enter a legacy array formula, or use SUMPRODUCT for array-style calculations.

Related formulas

ARRAYFORMULA (Google Sheets)FILTER IFERROR SUMIF

Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.

Last reviewed: May 28, 2026