The** IF** function is one of the most useful and widely used functions of Microsoft Excel. If we need any kind of logical comparison in our daily life work in Excel, we use the **IF** function. Today I will be showing how to use this **IF** function with a wide range of values, accompanied by some familiar functions in Excel.

**Table of Contents**Expand

**Introduction of IF Function in Excel**

One of the most useful functions in Excel is **the IF function**, which enables us to compare values logically to expectations.

**⇒ Syntax**

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

**⇒ Function Objective**

This determines if a condition is true or **FALSE**, and returns one value if the condition is **TRUE**.

**⇒ 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 whether or not the conditions are satisfied.

Let’s see some examples of Excel **IF** functions with a range of values. Suppose, we have a data set with the names, authors, numbers, and prices of some books from a bookshop called *Kingfisher Bookshop*. Our objective today is to learn how to apply Excel** IF** function with range of values.

**1. Applying Excel IF Function with Range of Cells**

In the first example, we will learn how to check if a range of cells contains a certain value or not. Let’s check whether there is any book by the author Emily Bronte or not. That means whether the column *Author* (column **C**) contains the name Emily Bronte or not. You can use a combination of the **IF** and **COUNTIF** functions of Excel to do that.

**STEPS:**

- Firstly, select a cell and enter this formula into that cell.

`=IF(COUNTIF(C5:C21,"Leo Tolstoy")>0,"There is", "There is Not")`

- Secondly, press
**Enter**to see the result. - Finally, you can see, we have got the result “
**There is**”. Because there is indeed a book by*Emily Bronte*on our list. That is “*Wuthering Heights*”.

- If you want an approximate match, you can use
**Wildcard Characters (*,?,~)**within the**COUNTIF**function. For example, 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")`

- Further, hit the
**Enter**key to show the outcome. - And, we have got “
**There is**”. Because there are three books written by the**Bronte Sisters**.

**NOTE:**The

**COUNTIF**function searches for a case-insensitive match. That is, 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.- Therefore
**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.

**2. Creating IF Function with Range of Numeric Values in Excel**

Now we shall apply another **IF** statement. We will create a list of values from a range that falls between two given numbers. Let’s find out the number of books that are there or not from column **D** that fall between **10** to **20**. These types of tasks can be accomplished using Excel** IF** function with a range of values.

**STEPS:**

- To begin with, select the cell where you want to see the result.
- Then, 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, to**AutoFill**the range, double-click on the plus (**+**) symbol.

- Finally, we can see the result.

**3. Applying AND Conditions with IF Function for Range of Values**

Now we will apply conditions within an **IF** function. Let’s check whether each of the books meets the two given conditions or not. The first one is, the number of books is greater than **10** and the second one is the price of the book is greater than **20**. If those conditions are fulfilled only then we will purchase the book.

For this, we will use the combination of **IF** and **AND** functions. When all of its parameters are evaluated as **TRUE**, **the AND function** returns **TRUE**; otherwise, it returns **FALSE**.

**STEPS:**

- In the beginning, select a cell adjacent to the first book and enter the formula.

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

- Press the
**Enter**key on your keyboard once more.

- Alternatively, we can use the symbol of
**AND**condition (*****) in the formula. Therefore, the formula will look like this.

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

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

- To copy the formula over the range, drag the
**Fill Handle**symbol downward. Alternatively, you can double-click the addition (**+**) sign to**AutoFill**the range.

- Similarly, we can get the result.

**4. Using Excel IF Function with OR Conditions for Range of Values **

Now come to **OR** type conditions. Let’s check whether each of the books satisfies at least one condition or not. If any of its parameters are evaluated as **TRUE**, **the OR function** returns **TRUE**; otherwise, it returns **FALSE**.

**STEPS:**

- Firstly, select the cell where we want to see the result.
- Secondly, insert the formula.

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

- Further, press the
**Enter**key from your keyboard.

- Instead of using the function, we can use the or symbol (
**+**). So, the formula will be.

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

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

- After that, drag the
**Fill Handle**icon to copy the formula over the range. Or, double-click on the plus (**+**) sign. This also duplicates the formula.

- Finally, we have identified for each book whether it can be purchased or not, if at least one condition or not.

**5. Using Nested IF Function for Range of Values in Excel**

In this example, we will use nested **IF** conditions. That means we shall apply one** IF** formula within another **IF** formula. Let me ask you to perform a job. For all the books, *check whether the price is greater than or equal to $30.00* or not first. If yes, check if the number is greater than or equal to **15** or not. Then, if still yes, check if the author’s name starts with the letter “**C**” or not. If still yes, return “**Satisfy**“. Otherwise, return “**Does not Satisfy**“.

**STEPS:**

- To start with, select the cell and insert the following formula there.

`=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.

- Likewise, in the previous examples, drag the
**Fill Handle**icon down to duplicate the formula over the range. Or, to**AutoFill**the range, double-click on the plus (**+**) symbol. - Finally, you can see, only
**five**books satisfy all three conditions simultaneously.

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

**6. Combining IF & SUM Functions in Excel for Range of Values**

We will combine **IF** and **SUM** functions in this example. **The SUM function** adds values using addition. Let’s follow the example.

**STEPS:**

- Select the second cell
**G6**, and put the formula into that selected cell.

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

- Then, 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 or not.**IF(SUM(D5:D21)>=80, “Good”, IF(SUM(D5:D21)>=50, “Satisfactory”, “Poor”))**reports the result. In our case, the result was “**Good**”.

**7. Joining IF & AVERAGE Functions for Range of Values**

The average of the numbers given as parameters is determined by **the AVERAGE function**. Let’s combine the **IF** and **AVERAGE** functions for this example.

**STEPS:**

- In the beginning, select the cell where we want to put the result. In our case, we will choose cell
**G6**. - Then, insert the formula into that cell.

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

- Further, press
**Enter**key from the keyboard. - Finally, you will get your result.

**🔎**** 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**”.

**8. Using IF & EXACT Functions to Match Range of Values ****in Excel**

**The EXACT function **returns **TRUE** if two text strings are identical and **FALSE** otherwise when comparing two text strings. Although it overlooks formatting discrepancies, **EXACT** is case-sensitive. Let’s integrate the **IF** and **EXACT** functions together to match a range of values.

**STEPS:**

- Choose the cell where we want to view the outcome.
- Add the following function formula after that.

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

- Press the
**Enter**key on your keyboard once more. - And, you can see this will show the result in the range.

**NOTE:**You don’t need to use the formula in each cell, this will automatically show results for the range of cells.

**🔎**** 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.

**9. Combining IF, AND & TODAY Functions to Get Date in Excel**

Suppose we want to check whether the arrival date is within 7 days or not; if the arrival date is within seven days only then we can purchase the book. For this, we will use the combination of **IF**, **AND,** and **TODAY** functions.

**STEPS:**

- Likewise, in the earlier examples, select the cell and then enter the formula there.

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

- Then, press
**Enter**.

- To copy the formula over the range, drag the
**Fill Handle**symbol downward. Alternatively, you can double-click the addition (**+**) sign to**AutoFill**the range. - Finally, this will show the result for each book in column
**F**.

**10. Obtaining Highest/Lowest Value by Combining IF, MAX & MIN Functions**

Assume that we want to compare the number of books with the first book. And we will find the highest and the lowest value of the total book. For this, we will use the combination of **IF**, **MAX** & **MIN** functions.

**STEPS:**

- Firstly, select the cell where we want to put the result.
- Then, insert the formula into that cell.

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

- Finally, press
**Enter**key from the keyboard.

**🔎**** 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. For instance, you may enter text into a formula that is expecting numbers. - If we relocate the formula cell or the reference cells the
**#REF!**the error will appear. The references in the formula are no longer valid. - The
**#NAME!**error will show you misspell the name of a function in your formula.

**Download Practice Workbook**

**Conclusion**

Those above examples assist you in learning the Excel** IF** function with a range of values. Do you have any questions? Feel free to ask us.

## Related Articles

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