Practice free

Logical functions

Logical functions test conditions and choose what to return. IF and its friends turn a sheet from a list of numbers into something that makes decisions.

19 functions in this category.

Start here

All logical functions

FunctionWhat it doesLevelSinceSpills?
AND Checks that all conditions hold at once. Mostly used inside IF to require several criteria together. Beginner 2007 or earlier No
BYCOL Runs a calculation on each column of an array and returns the results side by side, such as the max of every column. Advanced 2024 Yes
BYROW Runs a calculation on each row of an array, such as a row total, and returns the results stacked vertically. Advanced 2024 Yes
FALSE Returns FALSE. You can also type FALSE directly; the function exists for compatibility with other spreadsheet programs. Beginner 2007 or earlier No
IF The basic decision function: show one thing if a condition holds, another if not. It underpins most conditional logic in spreadsheets. Beginner 2007 or earlier No
IFERROR Wraps a formula and swaps any error for a friendlier result such as blank or zero. Common around lookups and divisions. Beginner 2007 or earlier No
IFNA Catches only #N/A, which is what a failed lookup returns, so other errors still show up and can be fixed. Beginner 2013 No
IFS Checks conditions in order and returns the result of the first one that is true, replacing a chain of nested IFs. Beginner 2019 No
LAMBDA Lets you define your own function in a formula and give it a name in Name Manager. Used for logic you repeat, and as the engine behind MAP, REDUCE and similar helpers. Advanced 2024 No
LET Assigns names to intermediate values inside one formula so you compute each only once. Makes long formulas shorter, faster and easier to read. Advanced 2021 No
MAKEARRAY Generates a grid by running a calculation for every row and column position, such as a multiplication table. Advanced 2024 Yes
MAP Applies a custom calculation to every element of one or more arrays. It is the way to run LAMBDA logic element by element. Advanced 2024 Yes
NOT Flips a logical value. Handy for reading a test the other way round, or negating ISBLANK-style checks. Beginner 2007 or earlier No
OR Checks whether at least one of several conditions holds, usually inside IF. Beginner 2007 or earlier No
REDUCE Walks through an array, carrying a running result, and returns only the end result. Use it for custom totals or combining items in a way SUM cannot. Advanced 2024 No
SCAN Like REDUCE, but returns the running result at each step. Good for running totals and cumulative calculations. Advanced 2024 Yes
SWITCH Compares one expression against a list of exact values and returns the matching result. Cleaner than nested IFs when testing one value for equality. Intermediate 2019 No
TRUE Returns TRUE. Typing TRUE directly does the same; the function exists for compatibility. Beginner 2007 or earlier No
XOR Exclusive OR: true when an odd number of inputs are true. With two inputs, it means one or the other but not both. Intermediate 2013 No

Other categories