Returns one of the given values based on logical conditions. IF (Value1 > 20, 2, Value2 > 40, 4, 6) IF (Value1 > 20; 2; Value2 > 40; 4; 6) returns 2 if Value1 has a value greater than 20, 4 if Value1 does not have a value greater than 20 and Value2 has a value greater than 40, and 6 otherwise.

Multiple conditions

IF supports multiple conditions. Consider this formula:

The preceding formula returns 4 if Value1 is greater than 20. Otherwise, it returns 2 if Value1 is less than 10. If Value1 is not greater than 20, and it is not less than 10, -1 is returned.

Spreadsheets often require you to use the IFS function to use multiple conditions. This IF function supports multiple conditions, though IFS may also be used.

Logical "and" (conjunction)

Use logical functions and logical operators to express more complex relationships. This formula returns "Success!" only if Value1 is greater than 20 and Value2 is less than 10:

The AND function is more familiar to spreadsheet users — the && operator is not commonly supported by spreadsheets — and produces the same result:

Logical "or" (disjunction)

This formula returns "Success!" if Value1 is greater than 20 or Value2 is less than 10:

The OR function is, again, more familiar to spreadsheet users than the || operator (which is not commonly supported by spreadsheets) and performs identically:

Logical "not" (negation)

To negate a logical value (turning FALSE into TRUE and vice versa), use the ! operator or the NOT function.

These formulas both return 2 only if Value1 is not odd:

Blank values

If a third parameter is not passed to IF, a blank value is returned if the condition does not evaluate to TRUE. Both these formulas return a blank value if Value1 is not 10:

To determine if a value is blank, use the ISBLANK function.

This formula returns "No value" if Value1 has a blank value, and the value of Value1 otherwise:

The specialized IFBLANK function may be used to write an equivalent, shorter formula:

ISDEFINED does the reverse. This formula returns "Filled out!" only if a value is associated with Value1 :

Arrays and IF

When initializing arrays, use IF to determine if an element should be part of the array.

This formula returns the array { 2, 4, 6, 8 }{ 2; 4; 6; 8 } only if Value1 is greater than 20, and returns the array { 2, 4, 8 }{ 2; 4; 8 } otherwise:

If the condition is a logical array, IF is applied to each of its elements, and returns an array. This formula returns the array { 0, 20, 30 }{ 0; 20; 30 }, as only the first element of { 1, 2, 3 }{ 1; 2; 3 } is not greater than 1:

Spreadsheet users often use this technique to filter values before adding them together. This formula returns 50:

Leaving out the third parameter gives a blank value for each element whose condition is FALSE, which functions like MAX ignore. This formula returns 30:

The FILTER function expresses the same idea more directly. These formulas return 50 and 30, respectively:

IF alternative: SWITCH

Consider using SWITCH instead of this function if you need to test for equality against the same value. These formulas are equivalent:

IF alternative: CHOOSE

CHOOSE selects which parameter to return based on the first parameter. If the first parameter is 3, the third parameter is returned. If it is 20, the 20th parameter is returned.

These formulas are equivalent: