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
- AND
Checks that all conditions hold at once. Mostly used inside IF to require several criteria together.
- IF
The basic decision function: show one thing if a condition holds, another if not. It underpins most conditional logic in spreadsheets.
- IFS
Checks conditions in order and returns the result of the first one that is true, replacing a chain of nested IFs.
- IFERROR
Wraps a formula and swaps any error for a friendlier result such as blank or zero. Common around lookups and divisions.
- NOT
Flips a logical value. Handy for reading a test the other way round, or negating ISBLANK-style checks.
- OR
Checks whether at least one of several conditions holds, usually inside IF.
All logical functions
| Function | What it does | Level | Since | Spills? |
|---|---|---|---|---|
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
- Lookup and reference functions
43 functions
- Math and trig functions
80 functions
- Text functions
48 functions
- Date and time functions
25 functions
- Statistical functions
111 functions
- Financial functions
56 functions
- Information functions
21 functions
- Database functions
12 functions
- Engineering functions
54 functions
- Web functions
3 functions
- Compatibility functions
41 functions
- Cube functions
7 functions
- Add-in and automation functions
5 functions