## IF function explained

** Table of Contents**

- Overview
- Example 1 - Logical test
- Example 2 - Value if TRUE/FALSE
- Example 3 - Nested IFs
- Example 4 - Many conditions
- Example 5 - Array formula
- 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

### 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.

### 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.

**Formula in cell E13:**

**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**"))

### Example 4 - Many conditions

**Formula in cell F3:**

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

### Example 5 - Array formula

**Array formula in cell H5:**

**How to enter an array formula**

- Select cell H5
- Paste array formula to formula bar
- Press and hold Ctrl + Shift
- Press Enter
- 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})

### Download excel *.xlsx file

### Category: Excel

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.Comments(445) Filed in category: Excel, VLOOKUP and return multiple values

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]Comments(249) Filed in category: Concatenate, Excel, Textjoin

Comments(161) Filed in category: Charts, Excel, Interactive

### 9 Responses to “IF function explained”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

[...] Interested in how the IF function works, read this post: IF function explained [...]

[…] 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 […]

[…] 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 […]

[…] 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 […]

[…] 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 […]

Hi Oscar

Thank you for your explanation and examples. I'd really appreciate it if you could you help me with this:

I'm trying to say, "if a cell from E2 to E89 contains 1, then sum the contents of the corresponding cell in D", ie. I only want to add D cell contents together if the corresponding E cell contains '1'.

=SUM(IF(E2:E89=1, D2:D89, 0))

Thank you!

Alison.

[…] If function […]

[…] want to sort text values only, the IF and ISTEXT functions check if a value is a text […]

[…] If the value in cell K23 is larger than cell K24 AND cell K24 is smaller than K25 THEN return the closing price. If not return nothing. Read more about IF function. […]