Can ARRAYFORMULA IF test multiple conditions?

Yes. In an array expression, Boolean multiplication can represent row-level `AND` logic: ```gs (B2:B<DATE(2026,8,1))*EXACT(C2:C,"Open") ``` Both tests must be true for the product to equal `1`. Nested `IF` functions can then handle validation and alternative outputs.

Why can't I enter manual results inside the array output range?

Existing content in the output range blocks the array formula from expanding and can produce a `#REF!` error. The formula doesn't overwrite that content. If you need manual overrides for selected rows, keep them in a separate input column or use row-level formulas instead.

Does the formula validate every possible invoice date?

No. It rejects text, zero, and negative values, but accepts any positive numeric serial. Add explicit date bounds if your process must reject dates outside a defined period.

Does ARRAYFORMULA remove or flag duplicate invoices?

No. It evaluates each row independently. Two identical input rows receive identical labels unless you add separate duplicate-detection logic.

Does this ARRAYFORMULA IF formula work in Excel?

Not unchanged. `ARRAYFORMULA` is a Google Sheets function. Excel supports array calculations through its own formula behavior and functions, but the appropriate Excel expression depends on the workbook structure and Excel version.

ARRAYFORMULA IF in Google Sheets: Finance Example

Q: Why can't I enter manual results inside the array output range?

Existing content in the output range blocks the array formula from expanding and can produce a `#REF!` error. The formula doesn't overwrite that content. If you need manual overrides for selected rows, keep them in a separate input column or use row-level formulas instead.

Q: Does the formula validate every possible invoice date?

No. It rejects text, zero, and negative values, but accepts any positive numeric serial. Add explicit date bounds if your process must reject dates outside a defined period.

Q: Does ARRAYFORMULA remove or flag duplicate invoices?

No. It evaluates each row independently. Two identical input rows receive identical labels unless you add separate duplicate-detection logic.

Q: Does this ARRAYFORMULA IF formula work in Excel?

Not unchanged. `ARRAYFORMULA` is a Google Sheets function. Excel supports array calculations through its own formula behavior and functions, but the appropriate Excel expression depends on the workbook structure and Excel version.

Editorial illustration

The basic structure is:

=ARRAYFORMULA(IF(condition_range, value_if_true, value_if_false))

Here, IF determines what each row returns, while ARRAYFORMULA evaluates the ranges as arrays. Google documents both the ARRAYFORMULA and IF functions.

The following example creates an invoice-status column that returns Overdue, Current, Check date, or Check status without requiring you to copy the formula down.

Set up the invoice data

Create a Google Sheets tab named Invoices with these headers:

Cell	Header
A1	Invoice ID
B1	Due date
C1	Status
D1	Result

Use a fixed reporting boundary of August 1, 2026. An invoice is overdue only when:

Its due date is earlier than August 1, 2026.
Its status is exactly Open.

Because the comparison uses < rather than <=, an invoice dated August 1 is current. The valid status values are the case-sensitive entries Open and Paid.

Enter these illustrative inputs:

Row	Invoice ID	Due date entry	Status
2	INV-1001	`=DATE(2026,7,15)`	Open
3	INV-1002	`=DATE(2026,8,1)`	Open
4	INV-1003	`=DATE(2026,7,20)`	Paid
5	blank	blank	blank
6	INV-1004	`TBD` as text	Open
7	INV-1005	`=DATE(2026,7,20)`	open
8	INV-1006	`0`	Open
9	INV-1007	`=DATE(2026,7,20)`	blank

Intended due dates should be Google Sheets date values, not text that resembles a date. Although the formula checks whether each value is a positive number, it doesn't confirm that the number falls within a reasonable invoice-date range.

Enter the ARRAYFORMULA IF formula

First, make sure D3:D is empty. Then enter this formula only in D2:

=ARRAYFORMULA(IF(B2:B="","",IF(NOT(ISNUMBER(B2:B)),"Check date",IF(B2:B<=0,"Check date",IF((EXACT(C2:C,"Open")+EXACT(C2:C,"Paid"))=0,"Check status",IF((B2:B<DATE(2026,8,1))*EXACT(C2:C,"Open"),"Overdue","Current"))))))

For every row, the formula follows the same sequence:

IF(B2:B="","",...) leaves the result blank when the due date is blank.
NOT(ISNUMBER(B2:B)) sends text such as TBD to Check date.
B2:B<=0 sends zero or negative values to Check date.
Positive numeric values proceed as date serials; the formula doesn't impose minimum or maximum acceptable dates.
The two EXACT checks accept only the case-sensitive statuses Open and Paid.
An invalid or missing status returns Check status.
Multiplication acts as a row-level AND: the due date must be before August 1 and the status must be exactly Open.
Rows that pass these checks but aren't overdue return Current.

Verify the expected results

The expected output is:

Cell	Expected result	Reason
D2	Overdue	July 15 is before August 1 and the status is `Open`.
D3	Current	August 1 is not earlier than August 1.
D4	Current	The invoice is `Paid`, even though its due date is earlier.
D5	blank	The due date is blank.
D6	Check date	`TBD` is text rather than a numeric date value.
D7	Check status	Lowercase `open` doesn't exactly match `Open`.
D8	Check date	Zero fails the positive-value check.
D9	Check status	The populated row has no valid status.

These are independently calculated expected results and suggested verification checks, not a claim that the formula was executed in your workbook. After entering it, compare D2:D9 with this table.

A few additional boundary checks help clarify the formula's scope:

A positive number such as 1 with status Open returns Overdue. Google Sheets treats dates as numeric serials, and this formula doesn't determine whether a positive serial is a plausible invoice date.
If every populated row that passes the checks is Paid or dated on or after August 1, each row returns Current. The formula doesn't generate a separate “no matches” message.
Duplicate invoice rows receive the same result independently. This formula doesn't identify or remove duplicates.

An opening-entry or balance test isn't applicable because the example performs no aggregation or financial-statement reconciliation.

Why the blank check comes first

Without this part:

IF(B2:B="","",...)

the formula would also evaluate unused rows. As a result, it could fill the output column with labels or error messages below the invoice table.

In this example, the due date determines whether a row is treated as populated. Any row with a blank due date returns blank, even if another cell in that row contains data.

If that rule doesn't suit your workbook, use a different required field, such as the invoice ID:

A2:A=""

Whichever field you choose, keep all array ranges aligned from the same starting row.

ARRAYFORMULA versus copying down a regular IF

The equivalent row-level formula for D2 is:

=IF(B2="","",IF(NOT(ISNUMBER(B2)),"Check date",IF(B2<=0,"Check date",IF((EXACT(C2,"Open")+EXACT(C2,"Paid"))=0,"Check status",IF(AND(B2<DATE(2026,8,1),EXACT(C2,"Open")),"Overdue","Current")))))

You must copy this version from D2 into each required row.

Method	Formula placement	Main trade-off
`ARRAYFORMULA(IF(...))`	One formula in D2	New rows are covered automatically, but the output range must remain clear.
Row-level `IF`	One formula per row	Each row can be edited separately, but formulas must be copied down and can become inconsistent.

For a standardized calculated column, the array version gives you one formula to inspect and maintain. A row-level formula may be more suitable when individual rows intentionally need different logic.

Common ARRAYFORMULA IF errors

The array result can't expand

Content anywhere in the required output area can block expansion and produce a #REF! error. Clear existing values and formulas from D3:D while preserving the formula in D2.

Conditions are applied to the wrong rows

Every array range must begin on the same row. This is aligned:

B2:B
C2:C

This is not:

B2:B
C3:C

When the starting rows don't match, the formula can compare one invoice's due date with another invoice's status.

Dates are stored as text or use an unexpected serial

A displayed date isn't necessarily a numeric Google Sheets date. In this example, text such as TBD returns Check date. Numeric zero does too.

However, any positive numeric value proceeds to the status and overdue tests. If your model must reject dates outside an acceptable reporting period, add explicit lower and upper bounds to the formula.

Status spelling or case differs

Because EXACT makes the validation case-sensitive, only Open and Paid are valid. Values such as open, PAID, other labels, and blank statuses on populated rows return Check status.

Google Sheets reports a formula parse error

The formula shown here uses commas as argument separators. Some spreadsheet locales require semicolons. If yours does, replace the argument-separating commas with semicolons while leaving the logic unchanged.

Get formula help from ModelMonkey

ModelMonkey is an AI assistant for spreadsheet work in Google Sheets and Microsoft Excel. It can inspect workbook context and help with formulas. You should review its output against your intended rules and expected results.

For this example, you could make a specific request:

In the Invoices sheet, create one formula in D2 that labels invoices as Overdue or Current using August 1, 2026 as the boundary. Leave blank due-date rows empty, flag text or nonpositive dates as Check date, and accept only the exact statuses Open and Paid. Show the proposed formula and expected D2:D9 results for review.

Since ARRAYFORMULA is Google Sheets syntax, you shouldn't assume the same expression will work unchanged in Excel. For more information about formula assistance, see Google Sheets AI Formula Tools for FP&A.