IF and IFS functions in Google Sheets

IF returns one value when a condition is true and another when it is false; IFS checks several conditions in turn.

IF is how a sheet decides. It asks a question with a yes-or-no answer and shows one thing for yes and another for no. IFS handles several questions in a row without brackets nesting inside each other.

Syntax

=IF(logical_expression, value_if_true, value_if_false)

=IFS(condition1, value1, [condition2, value2], ...)

Argument	What it is
`logical_expression`	A test that is true or false, such as C2>=33.
`value_if_true, value_if_false`	What to return in each case.
`condition1, value1, ...`	For IFS: pairs of test and result. The first true test wins.

Examples

Sample sheet: Marks of eight students in three subjects. One Science mark is missing (cell D6 is empty).

	A	B	C	D	E
1	Name	Class	Maths	Science	English
2	Asha	8A	78	85	91
3	Ravi	8B	45	52	60
4	Meena	8A	92	88	79
5	Kabir	8B	33	41	38
6	Tara	8A	67		72
7	Joy	8B	88	90	84
8	Neha	8A	54	61	58
9	Imran	8B	78	73	69

=IF(C2>=33,"Pass","Fail")
Result: Pass
78 is at least 33.
=IF(D6="","Absent",D6)
Result: Absent
An empty cell is tested with two quotation marks.
=IF(C2>=75,"Distinction",IF(C2>=33,"Pass","Fail"))
Result: Distinction
An IF inside an IF, for three outcomes.
=IFS(C2>=90,"A",C2>=75,"B",C2>=50,"C",C2>=33,"D",TRUE,"Fail")
Result: B
IFS as a flat list. 78 fails the first test and passes the second.
=IFS(C3>=90,"A",C3>=75,"B",C3>=50,"C")
Result: #N/A
No test is true and there is no catch-all, so the result is #N/A.
=IF(AND(C4>=75,D4>=75,E4>=75),"Merit","")
Result: Merit
AND requires all three tests.
=ARRAYFORMULA(IF(C2:C5>=50,"Pass","Fail"))
Result:
Pass
Fail
Pass
Fail
In ARRAYFORMULA, IF works down a whole column.

IF

Three parts: the test, the result when it is true, the result when it is false.

The test is a comparison using =, <>, >, <, >= or <=. Text inside the formula goes in double quotation marks. Numbers and cell references do not.

In Sheets, the third argument may be left out. A false test then returns FALSE. Returning "" leaves the cell looking empty.

IFS

IFS takes pairs: a test, then the result if it is true. It reads them from left to right and stops at the first true one. Order therefore matters. Test the highest boundary first.

IFS has no "otherwise". If no test is true, it returns #N/A. End the list with TRUE and a default value to catch everything else, as in the fourth example.

AND, OR, NOT

Combine tests inside IF: AND(a, b) needs both, OR(a, b) needs one, NOT(a) reverses.

IF down a whole column

Inside ARRAYFORMULA, IF evaluates row by row:

=ARRAYFORMULA(IF(A2:A="", "", IF(C2:C>=33, "Pass", "Fail")))

The outer IF keeps empty rows empty. This pattern, a guard on a column that is always filled, appears in almost every self-filling column in Sheets.

Two cautions for arrays:

AND and OR do not work row by row. They collapse the whole column to one answer. Multiply tests for "and", (C2:C>=33)*(D2:D>=33), and add them for "or", ((C2:C>=90)+(D2:D>=90))>0.
IFS inside ARRAYFORMULA works, but every branch is calculated for every row, and an error in any branch shows through. Nested IFs are more forgiving.

Blank is not zero

An empty cell compared with a number counts as 0, so D6>=33 is false for an absent student, and the formula would say "Fail". Test for the blank first, as the second example does.

Tick boxes

A tick box in Sheets holds TRUE or FALSE. That makes it a ready-made test: =IF(B2, "Done", "Pending").

Other ways to choose

SWITCH compares one value with a list of exact matches: =SWITCH(C2, "N", "North", "S", "South", "Other").
A lookup table with VLOOKUP in sorted mode suits bands that may change, such as grade boundaries.
REGEXMATCH inside IF tests the shape of a text.

Common mistakes

Text without quotation marks, giving #NAME?.
Writing 33<=C2<=100. Use AND(C2>=33, C2<=100).
IFS without a final TRUE.
Tests in the wrong order, so that an early wide one catches everything.
Numbers in quotation marks, which become text and will not add up.

In Excel

IF and IFS are the same in Excel. IFS needs Excel 2019 or later. In Excel, IF's value_if_false is also optional.

Related functions

The results on this page were worked out by us from the sample sheet; they were not produced by a script running Google Sheets. If Sheets shows you something different, please tell us.

This is an independent help site. It is not connected with or approved by Google. Google Sheets is a trademark of Google LLC, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.