# How to use IF with ARRAYFORMULA in Google Sheets

Give IF whole ranges and wrap it in ARRAYFORMULA: =ARRAYFORMULA(IF(B2:B9="","",IF(B2:B9>=F1,"Pass","Fail"))). One formula fills the column.

**Google Sheets:** `=ARRAYFORMULA(IF(B2:B9="","",IF(B2:B9>=F1,"Pass","Fail")))`

**Excel:** Excel 365 and 2021 spill arrays on their own, so drop ARRAYFORMULA: `=IF(B2:B9="","",IF(B2:B9>=F1,"Pass","Fail"))`. In older Excel, put `=IF(B2="","",IF(B2>=$F$1,"Pass","Fail"))` in C2 and fill it down.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Student | Score | Result |  | Pass mark | 60 |
| Ava | 72 |  |  |  |  |
| Ben | 58 |  |  |  |  |
| Cleo | 91 |  |  |  |  |
| Dan |  |  |  |  |  |
| Eve | 60 |  |  |  |  |
| Finn | 47 |  |  |  |  |
| Gia | 83 |  |  |  |  |
| Hal |  |  |  |  |  |

Formula in C2: `=ARRAYFORMULA(IF(B2:B9="","",IF(B2:B9>=F1,"Pass","Fail")))`

Result (spills 8 × 1): Pass; Fail; Pass; ; Pass; Fail; Pass; 

## How it works

ARRAYFORMULA makes IF work on the whole range at once and spill one result per row from a single cell. The outer IF returns "" for empty scores, so rows still waiting for data stay blank instead of showing Fail.

## Other ways

- `=IF(B2:B9="","",IF(B2:B9>=F1,"Pass","Fail"))`: The Excel 365 version, which spills without ARRAYFORMULA.
- `=ARRAYFORMULA(IF(B2:B9>=F1,"Pass","Fail"))`: Without the blank check. (Empty rows show "Fail".)

## Common mistakes

- AND and OR don't work row by row inside ARRAYFORMULA: they collapse the whole range into one value. Multiply for AND and add for OR: `IF((B2:B9>=60)*(C2:C9="Yes"),…)`.
- An open-ended range like `B2:B` reaches the bottom of the sheet. Keep the blank check, or every empty row shows a result.
- Cells in the spill area must be empty, or you get #REF! with "Array result was not expanded".

## Questions

**How do I put the header in the same formula?**

Stack it on top in C1: `={"Result";ARRAYFORMULA(IF(B2:B9="","",IF(B2:B9>=F1,"Pass","Fail")))}`.

**Why does my ARRAYFORMULA return only one value?**

Something inside collapses the range, usually AND, OR or SUM. Replace AND and OR with * and +.

**Can I nest several IFs?**

Yes, they all run per row: `=ARRAYFORMULA(IF(B2:B9="","",IF(B2:B9>=90,"Distinction",IF(B2:B9>=F1,"Pass","Fail"))))`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/if-with-arrayformula