The IF function returns one value if the logical test is TRUE and another value if the logical test is FALSE.

Excel function syntax

IF(logical_test, [value_if_true], [value_if_false])

Arguments

logical_test	Required. The logical expression determines what value the IF function returns.  Excel evaluates all numerical values positive or negative to TRUE except 0 (zero) that equals FALSE.
[value_if_true]	Optional. The value the IF function returns if the logical expression evaluates to TRUE. If omitted 0 (zero) is returned.
[value_if_false]	Optional. The value the IF function returns if the logical expression evaluates to FALSE. If omitted 0 (zero) is returned.

Comment

The IF function is one of the most used functions in Excel.

Table of Contents

1. Logical expression
2. Logical operators
3. Use an Excel function to perform a logical test
4. Working with arrays

Logical expression

The logical_test argument in the IF function determines the outcome meaning if the second argument [value_if_true] or third argument [value_if_false] will be returned or calculated.

Boolean value TRUE or FALSE are valid outcomes, however, their numerical equivalents are also valid. For example, 0 (zero) is evaluated as FALSE and all other numerical values, even negative values, are evaluated as TRUE.

Formula in cell B3:

The logical_test argument in the formula above is 10/20 which equals 0.5. 0.5 is not equal to 0 (zero) so the formula returns TRUE in cell B3.

Formula in cell B4:

The logical_test argument in cell B4 above is 0 (zero). 0 (zero) equals 0 (zero) so the formula returns FALSE in cell B3.

Formula in cell B5:

The logical_test argument in the formula above is 1. 1 is not equal to 0 (zero) so the formula returns TRUE in cell B3.

Formula in cell B6:

The logical_test argument in cell B6 above is -1. -1 is not equal to 0 (zero) and the formula returns TRUE in cell B3.

The formula above returns #VALUE! error which means that text strings are not allowed as a logical test, however a logical expression using text strings are fine, see example below.

This formula will return TRUE because "A" is equal to "A".

Logical operators

The logical_test argument allows you to use comparison operators, these characters let you to do more advanced comparisons than the equal sign.

• = (equal sign)
• < less than
• > greater than
• <= less than or equal to
• >= greater than or equal to
• <> not equal

Formula in cell C3:

This formula compares the value in cell B3 with number 5, if equal TRUE is returned. Note that this formula is only for demonstration purposes, you can use this formula in cell C3 to get the same result =B3=5.

If you change the value in cell B3 the formula in cell C3 instantly recalculates and returns TRUE or FALSE based on the outcome of the logical_test.

Formula in cell C4:

The formula in cell C4 checks if the value in cell B4 is larger than 5, cell B4 contains 3 and 3 is not larger than 5 so the formula returns FALSE.

Formula in cell C5:

This formula evaluates if the value in cell B5 is smaller than 5, the image above shows that cell B5 contains number 6. 6 is not smaller than 5 and the formula returns FALSE.

Formula in cell C6:

The formula in cell C6 evaluates if the value in cell B6 is smaller than or equal to 5. Cell B6 contains 2, see image above, and 2 is smaller than or equal to 5. The formula returns TRUE in cell C6.

Formula in cell C7:

This formula checks if value in cell B7 is larger than or equal to 5, cell B7 contains 5 and 5 is equal to 5. The formula returns TRUE in cell C7.

Formula in cell C8:

The formula in cell C8 assesses if value in cell B8 is not equal to 5, cell B8 contains 5. 5 is equal to 5 and the formula returns FALSE in cell C8.

Use an Excel function to perform a logical test

You are not limited to comparison operators when dealing with the logical_test argument, any function that returns TRUE or FALSE can be used.

The picture above shows a formula in cell C3 that evaluates if cell B3 is blank. It is not blank so it returns FALSE.

Cell B4 is blank and the IF function returns TRUE in cell C4.

The following formula uses the ISNUMBER function to identify the value in cell B5.

The formula in cell C5 returns TRUE because the value in cell B5 is 6 and that is a number.

Cell C7 and C8 contain formulas that test if B7 and B8 contain a formula. Formula in cell C8 returns TRUE, cell B8 contains this formula =5+1.

Check out the functions in the Information category, many of them return TRUE or FALSE.

Working with arrays

The image above demonstrates a formula in cell F5 that uses the specified value in cell F3 to filter values from column C based column B.

If the year in column B matches the value in cell F3 the corresponding value on the same row is used. The formula is actually performing this calculation to all values in cell range B3:B10.

Array formula in cell F5:

This is an array formula, to enter an array formula press and hold CTRL + SHIFT simultaneously after you have entered the formula in a cell. Press Enter once and then release release all keys.

The formula changes and now has curly brackets surrounding the formula, like this: {=MEDIAN(IF(B3:B10=F3,C3:C10,""))}

Do not enter these characters your self, they appear automatically. Note that Office 365 subscribers do not need to enter this as an array formula, simply enter it a s a regular formula. This is a new feature in Excel called Dynamic arrays.

The IF function compares all values in cell range B3:B10 to the value in cell F3 and returns the corresponding value from cell range C3:C10 if they match. Nothing "" is returned if they don't match.

IF(B3:B10=F3,C3:C10,"")

becomes

IF({2012;2011;2012;2012;2011;2012;2012;2011}=2012, C3:C10, "")

becomes

IF({2012; 2011; 2012; 2012; 2011; 2012; 2012; 2011}=2012, C3:C10, "")

becomes

IF({TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE}, C3:C10, "")

becomes

IF({TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE}, {3; 76; 56; 51; 12; 6; 13; 36}, "")

and returns {3; ""; 56; 51; ""; 6; 13; ""}.

The MEDIAN function returns the middle value from the array.

MEDIAN(IF(B3:B10=F3,C3:C10,""))

becomes

MEDIAN({3; ""; 56; 51; ""; 6; 13; ""})

and returns 13 in cell F5.