**Introduction to the IF Function in Excel**

**⇒ Syntax**

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

**⇒ Function Objective**

Determines if a condition is **TRUE** or **FALSE**, then returns the corresponding value.

**⇒ Argument**

Argument | Required/Optional | Explanation |
---|---|---|

logical_test |
Required |
Given condition for a cell or a range of cells. |

[value_if_true] |
Optional |
Defined statement if the condition is met. |

[value_if_false] |
Optional |
Defined statement if the condition is not met. |

**⇒ Return Parameter**

If statements are not defined, logical values are **TRUE **or **FALSE**. If statements are defined, they will appear as return values depending on the condition.

**Example 1 – Applying the Excel IF Function with Range of Cells**

We’ll check whether there is any book by the author Emily Bronte.

**STEPS:**

- Select a cell and enter this formula into that cell.

`=IF(COUNTIF(C5:C21,"Emily Bronte`

`")>0,"There is", "There is Not")`

- Press
**Enter**to see the result.

- If you want an approximate match, you can use
**Wildcard Characters (*,?,~)**within the**COUNTIF**function. To find out whether there is any book by the*Bronte*sisters (*Both Emily Bronte and Charlotte Bronte*), use the following formula:

`=IF(COUNTIF(C4:C20,"*Bronte")>0,"There is", "There is Not")`

- Hit the
**Enter**key to show the outcome.

**NOTE:**The

**COUNTIF**function searches for a case-insensitive match. If you use the formula

**IF(COUNTIF(C5:C21,”emily bronte”)>0,”There is”, “There is Not”),**it will still return “

**There is**”.

** How Does the Formula Work?**

**COUNTIF(C5:C21,”Emily Bronte”)**returns the number of times the name “Emily Bronte” appears in the range**C5:C21**.**COUNTIF(C5:C21,”Emily Bronte”)>0**returns**TRUE**if the name appears at least once in the range, and returns**FALSE**if the name doesn’t appear.**IF(COUNTIF(C5:C21,”Emily Bronte”)>0,”There is”, “There is Not”)**returns “There is” if the name appears at least once, and returns “**There is Not**” if the name does not appear.

**Example 2 – IF Function with a Range of Numeric Values in Excel**

We will create a list of values from a range that falls between two given numbers. Let’s check if their prices fall between $10 and $20.

**Steps:**

- Select the cell where you want to see the result.
- Enter the formula there.

`=IF(((D5>=10)*(D5<=20))=1, "Yes", "No")`

- Press
**Enter**.

- Drag the
**Fill Handle**icon down to duplicate the formula over the range or double-click on the plus (**+**) symbol.

- Here’s the result.

**Example 3 – Applying AND Conditions with the IF Function for a Range of Values**

Let’s check two conditions: the number of books is greater than **10** and the price of the book is greater than **20**.

**Steps:**

- Select a cell F5 and enter the formula:

`=IF(AND(D5>=10,E5>=20),"Can Purchase","Can not Purchase")`

- Press the
**Enter**key.

- Alternatively, use the following formula:

**=IF((D5>=10)*(E5>=20),”Can Purchase”,”Can not Purchase”)**

- Drag the
**Fill Handle**symbol down or double-click it to**AutoFill**the range.

- Here’s the result.

**Example 4 – Using the IF Function with OR Conditions for a Range of Values **

**Steps:**

- Select the first cell where we want to see the result.
- Insert the formula.

`=IF(OR(D5>=10,E5>=60),"Can Purchase","Can not Purchase")`

- Press the
**Enter**key.

- Alternatively, use the following formula:

`=IF((D5>=10)+(E5>=60),"Can Purchase","Can not Purchase")`

- Hit
**Enter**to see the result.

- Drag the
**Fill Handle**icon to copy the formula over the range or double-click the Fill Handle.

- Here’s the result.

**Example 5 – Using a Nested IF Function for a Range of Values in Excel**

We’ll check whether the price is higher than $30, then check if the number of books is higher than 15. After that, we’ll check if the author’s name starts with the letter **C.** If all of these apply, we’ll return “**Satisfy**“. Otherwise, we’ll return “**Does not Satisfy**“.

**Steps:**

- Select the first result cell and insert the following formula:

`=IF(E5>=20,IF(D5>=15,IF(LEFT(C5,1)="C","Satisfy","Does not Satisfy"),"Does not Satisfy"),"Does not Satisfy")`

- Hit the
**Enter**key to see the outcome.

- Drag the
**Fill Handle**icon down to duplicate the formula over the range.

**Read More: **How to Use IF Function with Multiple Conditions in Excel

**Example 6 – Combining IF and SUM Functions in Excel for a Range of Values**

**Steps**

- Select
**G6**and put the following formula into it:

`=IF(SUM(D5:D21)>=80, "Good", IF(SUM(D5:D21)>=50, "Satisfactory", "Poor"))`

- Press the
**Enter**key to see the outcome.

** How Does the Formula Work?**

**SUM(D5:D21)**this part adds the values of the range and returns the total number of books as a result.**SUM(D5:D21)>=80**and**SUM(D5:D21)>=50**checks whether the condition is met.**IF(SUM(D5:D21)>=80, “Good”, IF(SUM(D5:D21)>=50, “Satisfactory”, “Poor”))**reports the result. In our case, the result was “**Good**”.

**Example 7 – Joining IF and AVERAGE Functions for a Range of Values**

**Steps:**

- Use the following formula in G6:

`=IF(AVERAGE(D5:D21)>=20, "Good", IF(AVERAGE(D5:D21)>=10, "Satisfactory", "Poor"))`

- Hit Enter.

** How Does the Formula Work?**

**AVERAGE(D5:D21)**calculates the average number of books.**AVERAGE(D5:D21)>=20**and**AVERAGE(D5:D21)>=10**verify whether the condition was satisfied.**IF(AVERAGE(D5:D21)>=20, “Good”, IF(AVERAGE(D5:D21)>=10, “Satisfactory”, “Poor”))**reveals the outcome. The outcome in our situation is “**Satisfactory**”.

**Example 8 – Using IF and EXACT Functions to Match a Range of Values ****in Excel**

**Steps:**

- Insert the following formula in the result cell.

`=IF(EXACT($C$5:$C$21,"Leo Tolstoy"), "Yes", "No")`

- Hit Enter (use Ctrl + Shift + Enter for Excel versions other than Excel 365).

** How Does the Formula Work?**

**EXACT($C$5:$C$21,”Leo Tolstoy”)**shows whether both data are an exact match or not.**IF(EXACT($C$5:$C$21,”Leo Tolstoy”), “Yes”, “No”)**check the logic and return the result.

**Example 9 – Combining IF, AND, and TODAY Functions to Get a Date in Excel**

We want to check whether the arrival date is within 7 days.

**Steps:**

- Use the following formula:

`=IF(AND(E5>TODAY(), E5<=TODAY()+7), "Yes", "No")`

- Press
**Enter**.

- Copy the formula over the result range with the Fill Handle.

**Example 10 – Obtaining Highest or Lowest Value by Combining IF, MAX, and MIN Functions**

**Steps:**

- Select the cell where we want to put the result.
- Insert the following formula into that cell.

`=IF(D5=MAX($D$5:$D$21), "Good", IF(D5=MIN($D$5:$D$21), "Not Good", " Average"))`

- Press
**Enter.**

** How Does the Formula Work?**

**MAX($D$5:$D$21)**returns the maximum value of the range.**MIN($D$5:$D$21)**returns the minimum value of the range.**IF(D5=MAX($D$5:$D$21), “Good”, IF(D5=MIN($D$5:$D$21), “Not Good”, ” Average”))**shows the result after comparison.

**Things to Remember**

- If you’re attempting to divide a number by zero in your formula, you may see
**#DIV/0!**error. - The
**#VALUE!**error occurs when you enter the incorrect data type into the calculation. - If we relocate the formula cell or the reference cells, the
**#REF!**the error will appear. - The
**#NAME!**error will show you misspell the name of a function in your formula.

**Download the Practice Workbook**

## Related Articles

**<< Go Back to Excel IF Function | Excel Functions | Learn Excel**