How to Use IF Function of Excel with Range of Values

COUNTIF Function with Wildcard Characters to Search for an Approximate Match

IF is one of the most useful and widely used functions of Excel. Today I will be showing how you can use this IF function with a wide range of values, accompanied by some familiar functions like FILTER, COUNTIF etc.

Download Practice Workbook

How to Use the IF Function of Excel with Range of Values

Here we have a data set with the names, authors, number and price of some books of a bookshop called Kingfisher Bookshop.

Data Set for Excel IF Function with Range of Values

Our objective today is to learn how to apply various IF statements on a range of cells like this.

1. Checking If a Range of Cells Contains a Value or Not

First of all, 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.

Select a new cell and enter this formula:

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

Checking If a Value is in a Range of Cells

See, we have got the result as “There is”.

Because there is indeed a book by Emily Bronte on our list. That is “Wuthering Heights”.

Explanation of the Formula

  • COUNTIF(C4:C20,"Emily Bronte") returns the number of times the name “Emily Bronte” appears in the range C4:C20.
  • COUNTIF(C4:C20,"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(C4:C20,"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.

Notes

  • COUNTIF function searches for a case-insensitive match. That is, if you use the formula IF(COUNTIF(C4:C20,"emily bronte")>0,"There is", "There is Not"), it will still return “There is”.
  • 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 this formula:

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

COUNTIF Function with Wildcard Characters to Search for an Approximate Match

See, we have got “There is”.

Because there are three books written by the Bronte Sisters.

2. Creating a List of Values between Two Numbers

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 prices from column E that fall between $20.00 to $30.00.

These types of tasks can be accomplished using the IF function of Excel, but it is better to use the FILTER function instead.

Select a new cell and enter this formula:

=FILTER(E4:E20,(E4:E20>=20)*(E4:E20<=30))

FILTER function to Create a List of Numbers between Two Numbers

See, we have got a list of all the prices that are between $20.00 and $30.00.

Explanation of the Formula

  • (E4:E20>=20)*(E4:E20<=30) returns 1 when any price is greater than or equal to $20.00 and less than or equal to $30.00, otherwise it returns 0.
  • FILTER(E4:E20,(E4:E20>=20)*(E4:E20<=30)) returns the corresponding value from the range E4:E20 if (E4:E20>=20)*(E4:E20<=30) returns 1, otherwise it returns nothing.

Note

To get the names of the books that have prices between $20.00 and $30.00 other than the prices themselves, use this formula:

=FILTER(B4:B20,(E4:E20>=20)*(E4:E20<=30))

FILTER Function to Create a List of Books with Prices between Two Numbers

3. IF Statement with Multiple AND/OR Conditions

3.1 AND Type Conditions

Now we will apply multiple conditions within an IF function.

Let’s check whether each of the books meets two given conditions or not:

  1. Number of books is at least 15 or not.
  2. Price is at least $20.00 or not.

Select a new cell adjacent to the first book and enter this formula:

=IF(((D4>=15)*(E4>=20))=1,"Satisfy","Does not Satisfy")

Applying IF Function with Multiple AND Conditions

Then drag the Fill Handle to fill the rest of the cells.

Dragging Fill Handle of IF Function with Multiple AND Type Conditions

Explanation of the Formula

  • ((D4>=15)*(E4>=20)) returns 1 if both the conditions are fulfilled, otherwise it returns 0.
  • IF(((D4>=15)*(E4>=20))=1,"Satisfy","Does not Satisfy") returns “Satisfy” if ((D4>=15)*(E4>=20))=1, otherwise it returns “Does not Satisfy”.

3.2 OR Type Conditions

Now come to OR type conditions. What will be the formula to check whether each of the books satisfies at least one condition or not?

Just replace the “*” sign with a “+” and the “=1” with  “>=1”.

=IF(((D4>=15)+(E4>=20))>=1,"Satisfy","Does not Satisfy")

IF Function with Multiple OR Type Conditions

See, we have identified for each book whether it satisfies at least one condition or not.

4. Nested IF Conditions

This is our last task today. We shall 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.

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

This is an example of nested IF conditions.

The formula will be:

=IF(E4>=30,IF(D4>=15,IF(LEFT(C4,1)="C","Satisfy","Does not Satisfy"),"Does not Satisfy"),"Does not Satisfy")

Enter this formula in the first cell and then drag the Fill Handle.

Nested IF Function

See, only one book satisfies all the three conditions simultaneously.

Conclusion

Using these methods, we can use the IF function of Excel with any range of cells to check single, multiple, or nested conditions. Do you have any questions? Feel free to ask us.

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