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

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

excel if function range of values

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

excel if function range of values

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

excel if function range of values

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

excel if function range of values

  • Drag the Fill Handle icon down to duplicate the formula over the range. Or, to AutoFill the range, double-click on the plus (+) symbol.

excel if function range of values

  • Finally, we can see the result.

excel if function range of values


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.

excel if function range of values

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

excel if function range of values


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.

excel if function range of values

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

excel if function range of values


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.

excel if function range of values

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

excel if function range of values

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

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

excel if function range of values


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.


Further Readings

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo