Table of Contents

  1. Overview
  2. Example 1 - Logical test
  3. Example 2 - Value if TRUE/FALSE
  4. Example 3 - Nested IFs
  5. Example 4 - Many conditions
  6. Example 5 - Array formula
  7. Download excel *.xlsx file

Overview

IF function

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

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

Example 1 - Logical test

Any value or expression that returns TRUE or FALSE.

  • Ex 1, A1=5. If cell A1 is 5 the expression returns TRUE
  • Ex 2, You can also compare a cell value to a text string: A1="Car".
  • If a number is returned: 0 (zero) is FALSE and all values above 0 (zero) returns TRUE

if function - logical test

Example 2 - Value if TRUE/FALSE

The second argument (value_if_true) in the IF function is a value that is returned if the logical test returns TRUE. The third argument (value_if_false) in IF function is a value that is returned if the logical test returns FALSE.

The IF function can return a boolean value, number or a text string.

if function - value if true false

Example 3 - Nested IFs

You can nest IF functions up to 64 times (excel 2007 and above) but that is probably a bad idea. Example 4 shows you how to use many conditions without nesting.

if function - nested ifs

Formula in cell E13:

=IF(B13<3,"A",IF(B13>=6,"C","B"))

Explaining formula

=IF(B13<3,"A",IF(B13>=6,"C","B"))

becomes

=IF(4<3,"A",IF(B13>=6,"C","B"))

becomes

=IF(FALSE,"A",IF(B13>=6,"C","B"))

becomes

=IF(FALSE,"A",IF(4>=6,"C","B"))

becomes

=IF(FALSE,"A",IF(FALSE,"C","B"))

and returns B in cell E13.

Example 4 - Many conditions

if function - many conditions

Formula in cell F3:

=VLOOKUP(F1,C3:D6,2,TRUE)

The VLOOKUP function returns the closest value less or equal to the lookup_value.

Example 5 - Array formula

if function - array formula

Array formula in cell H5:

=MIN(IF(B3:B19=F3,C3:C19,""))

How to enter an array formula

  1. Select cell H5
  2. Paste array formula to formula bar
  3. Press and hold Ctrl + Shift
  4. Press Enter
  5. Release all keys

Explaining array formula in cell H5

MIN(IF(B3:B19=F3,C3:C19,""))

becomes

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

becomes

=MIN(IF({TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE},C3:C19,""))

becomes

=MIN(IF({TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; TRUE; TRUE},{3; 76; 56; 51; 12; 6; 13; 36; 36; 85; 12; 71; 73; 66; 35; 98; 20},""))

becomes

MIN({3;"";56;51;"";6;13;"";36;"";"";"";73;66;"";98;20})

and returns 3 in cell H5.

Download excel *.xlsx file

IF function.xlsx