How to Use an IF Function with Range of Values in Excel

This guide will discuss how to use an IF function with a range of values in Excel.

The rules for using the IF function in Excel are the following:

  • The IF function is used to run a logical test and returns one value for a TRUE result. Otherwise, it will return another value for a FALSE result.
  • The IF function is not case-sensitive.
  • The function cannot count values conditionally. So we would have to use the COUNTIF or COUNTIFS functions.
  • Additionally, the function cannot sum values conditionally. So we would have to use the SUMIF or SUMIFS functions.
  • When we input arrays as an argument to the function, it will evaluate each element in the array. 

Excel is a popular tool to use for many different purposes and situations. Since it has several built-in functions and tools, we can easily perform different complex calculations and manipulation of data. And one of the most helpful functions in Excel is the IF function.

In this guide, we will be focusing on learning how to use the IF function with a range of values in Excel. There are many things we can perform using the IF function. And we can also apply and use the function with a range of values and cells in Excel.

Let’s take a sample scenario wherein we need to use an IF function with a range of values in Excel.

Suppose we are updating an employee directory. So our task is to determine whether a certain employee is in the directory. Since you are dealing with a range of values, you utilized the IF function to run a logical test. 

In your formula, you wanted the formula to return “Present” if the employee is in the directory. Otherwise, you wanted the formula to return “Missing” if the employee is not in the directory. 

Before we move on to a real example of using an IF function with a range of values in Excel, let’s first learn how to write the IF function in Excel.

 

The Anatomy of the IF Function

The syntax or the way we write the IF function is as follows:

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

Let’s take apart this formula and understand what each term means:

  • = the equal sign is how we start any function in Excel.
  • IF() is our IF function. And the IF function is used to check whether a condition is met or not. Then, it will return one value if TRUE and another value if FALSE.
  • logical_test is the only required argument that can be any value or expression that can be evaluated as TRUE or FALSE.
  • value_if_true is an optional argument. And this refers to the value we want the function to return if the logical_test is TRUE. When omitted or left blank, the function will return TRUE.
  • value_if_false is another optional argument. So this refers to another value we want the function to return if the logical_test is FALSE. When omitted or left blank, the function will return FALSE. 

Great! Now we can dive into a real example of using an IF function with a range of values in Excel.

 

A Real Example of Using an IF Function with Range of Values in Excel

Let’s say we have a data set containing the employees’ names and the years they have worked in the company. So our initial data set would look like this:

Initial dataset

 

There are two tasks we want to accomplish in our data set. Firstly, we can check whether certain employees are present in the data set or not. Secondly, we will be checking whether there are employees in the data set that worked between the range of 3 years to 5 years.

Luckily, both of these tasks can easily be done using the IF function. So the IF function is used to run a logical test and return a certain value depending on if the logical test was TRUE or FALSE. In this case, we will use the IF function with a range of values. 

Firstly, we will check whether certain employees exist in our data set. To do this, we will utilize the IF function to run a logical test to determine if a certain employee is found in the data set. Afterward, we will input the values we want to return depending on the evaluation.

For instance, we want “Present” to be returned if the logical test is TRUE, meaning the employee is found in the data set otherwise, we want “Missing” to be returned if the logical test is FALSE, meaning the employee is not found in the data set.

Secondly, we will check whether there are employees who have worked between the range of 3 to 5 years. To do this, we will once again use the IF function to run a logical test. In this case, we will also input logical operators to express the range of years. 

For instance, it will be greater than or equal to 3 (>=3) and less than or equal to 5 (<=5). Moreover, there will be a multiplication symbol between the two, such as >=3*=5, to indicate that the function must meet both conditions.

Additionally, we want the returned value to be “Yes” if the employee worked within that range. Otherwise, the formula will return “No”. So our final data set would look like this:

Final dataset

 

You can make your own copy of the spreadsheet above using the link attached below. 

Amazing! Now we can proceed to discuss the steps of how to use an IF function with a range of values in Excel.

 

How to Use an IF Function with Range of Values in Excel

In this section, we will discuss the step-by-step process of how to use an IF function with a range of values in Excel. 

1. Firstly, we will check whether a certain employee exists in the data set. To do this, we can simply type in the formula “=IF(COUNTIF(B2:B10, “Kate”)>0, “Present”, “Missing”)”. Then, we will press the Enter key to return the result.

IF Function with Range of Values in Excel

 

2. Secondly, we will determine whether each employee has worked within the range of 3 years to 5 years. To do this, we can simply input the formula “=IF(((C2>=3)*(C2<=5))=1, “Yes”, “No”)”. Lastly, we will press the Enter key to return the result.

IF Function with Range of Values in Excel

 

3. Thirdly, we will drag down the Fill Handle tool to copy and apply the formula to the other cells.

IF Function with Range of Values in Excel

 

4. And tada! We have successfully used an IF function with a range of values in Excel.

IF Function with Range of Values in Excel

 

And that’s pretty much it! We have successfully discussed how to use an IF function with a range of values in Excel. Now you can go ahead and apply this method to your work whenever you need to work with a range of values.

Are you interested in learning more about what Excel can do? You can now use the IF function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like