Rifat Hassan
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.
Download Practice Workbook
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.
10 Ideal Examples to Use IF Function with Range of Values in Excel
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. Generate 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 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.
🔎 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. Create IF Function with Range of Numeric Values
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. Apply 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.
- 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. Utilize 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. Use Nested IF Function for Range of Values
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.
6. Combine IF & SUM Functions in Excel
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. Merge IF & AVERAGE Functions
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. Integrate IF & EXACT Functions to Match Range of Values
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.
🔎 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. Consolidate IF, AND & TODAY Functions to Get Date
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. Obtain 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.
Conclusion
Those above examples assist you to learn Excel IF function with a range of values. Do you have any questions? Feel free to ask us.
