How to Use Excel IF Function with Range of Values (10 Examples) - ExcelDemy (2024)

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.

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

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

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

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

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.

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

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

• Further, press the Enter key from your keyboard.

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

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

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

• Then, press the Enter key to see the outcome.

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.

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

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.

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

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.

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

• Finally, press Enter key from the keyboard.

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