How to use IF with ARRAYFORMULA in Google Sheets
Google Sheets formula
=ARRAYFORMULA(IF(B2:B9="","",IF(B2:B9>=F1,"Pass","Fail")))Give IF whole ranges and wrap it in ARRAYFORMULA: =ARRAYFORMULA(IF(B2:B9="","",IF(B2:B9>=F1,"Pass","Fail"))). One formula fills the column.
- Result on the example
- a 8-row list that spills from C2 in
C2 - Excel
- See below
See it work
The formula is already in C2. Change any input and the result updates, or edit the formula to experiment.
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.
=ARRAYFORMULA(IF(B2:B9="","",IF(B2:B9>=F1,"Pass","Fail")))Result{8 rows × 1 col} "Pass", "Fail", "Pass", "", …
B2:B98 rows × 1 col: 72, 58, 91, (empty), …A range: 8 cells in one column.
B2:B9=""{8 rows × 1 col} FALSE, FALSE, FALSE, TRUE, …The = operator tests whether the two sides are equal.
F160The value in F1.
B2:B9>=F1{8 rows × 1 col} TRUE, FALSE, TRUE, FALSE, …The >= operator tests at least.
IF(B2:B9>=F1,"Pass","Fail"){8 rows × 1 col} "Pass", "Fail", "Pass", "Fail", …IF: Returns one value if a test is true, another if false.- logical_test
B2:B9>=F1{8 rows × 1 col} TRUE, FALSE, TRUE, FALSE, … - value_if_true
"Pass" - value_if_false
"Fail"
- logical_test
IF(B2:B9="","",IF(B2:B9>=F1,"Pass","Fail")){8 rows × 1 col} "Pass", "Fail", "Pass", "", …IF: Returns one value if a test is true, another if false.- logical_test
B2:B9=""{8 rows × 1 col} FALSE, FALSE, FALSE, TRUE, … - value_if_true
"" - value_if_false
IF(B2:B9>=F1,"Pass","Fail"){8 rows × 1 col} "Pass", "Fail", "Pass", "Fail", …
- logical_test
ARRAYFORMULA(IF(B2:B9="","",IF(B2:B9>=F1,"Pass","Fail"))){8 rows × 1 col} "Pass", "Fail", "Pass", "", …ARRAYFORMULA: Runs a formula over whole ranges and returns all the results.- array_formula
IF(B2:B9="","",IF(B2:B9>=F1,"Pass","Fail")){8 rows × 1 col} "Pass", "Fail", "Pass", "", …
- array_formula
In Excel
=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.Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =IF(B2:B9="","",IF(B2:B9>=F1,"Pass","Fail"))Excel only | spills 8 × 1 | The Excel 365 version, which spills without ARRAYFORMULA. |
| =ARRAYFORMULA(IF(B2:B9>=F1,"Pass","Fail")) | spills 8 × 1Different result | 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:Breaches 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".
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Use IF with ARRAYFORMULA
C2, write one formula that returns "Pass" or "Fail" for every score in B2:B9 against the pass mark in F1, and stays blank for empty scores.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")))).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine.