Excel IF AND Function (Table of Contents)
IF AND Function in Excel
The aim of this tutorial is to show how to amalgamate IF and the AND functions in Excel in order to check several conditions in a single formula. Some of the items in nature are finite. Others seem to have endless potential, and interestingly the IF function seems like it has such properties.
Our objective today is to see how and if we can combine IF and the AND function so as to evaluate multiple conditions simultaneously. So first, we will need to build the IF AND function and to do that, we will be required to join two separate functions – the IF and AND functions, in one single formula. Let us see how this can be achieved:
IF(AND(Logical1, Logical2,…), val_if_true, val_if_false)
The above formula has the following meaning: If the first condition i.e. condition 1 is true and the second condition, i.e. condition 2 is also true, then do something, or else do something different.
How to Use IF AND Function in Excel?
Let’s understand how to use IF AND Function in Excel using some examples.
Excel IF AND Function – Example #1
Let us understand this through an example, let us create a formula whose objective is to check if B2 is “delivered” and also check that C2 isn’t empty. Now, depending on the outcome, it would do one of the following:
- If both condition 1 and condition 2 evaluates to TRUE, then the order has to be marked as “Closed”.
- If anyone of the conditions evaluates to FALSE or both evaluate to FALSE, then an empty string has to be returned (“”).
=IF(AND(B2=”delivered”, C2<>””), “Closed”, “”)
The IF AND function are demonstrated in the screenshot below:
4.8 (2,129 ratings)
View Course
Note: While using the IF AND formula, so as to evaluate certain conditions, it is important to keep in mind that uppercase and lowercase characters are considered as similar. So, if the objective is to use a case-sensitive version of the IF AND formula, we have to wrap the arguments of AND inside the EXACT function.
Excel IF AND Function – Example #2
So far in our earlier example, we were attempting to test two conditions different cells. But in our daily lives, we often encounter situations where we may be required to run multiple tests on the same cell. An example that typically demonstrates this is checking if a cell value falls in between two numbers. That is where the IF AND function comes in handy!
Now let us suppose that we have the sales numbers for certain orders in column B and our job is to flag the amounts that are less than $100 but greater than $50. So, to approach this problem, first we will insert the following formula in cell C2 and then proceed to copy it down the columns:
=IF(AND(B2>50, B2<100), “X”, “”)
Excel IF AND Function – Example #3
If it is required that we include the peripheral values (100 and 50), then we will need to make use of greater than or equal to (>=) operator and the less than or equal to operator (<=):
=IF(AND(B2>=50, B2<=100), “X”, “”)
Now moving on, in order to process other kinds of peripheral values without having to change the formula, we will input the maximum and minimum numbers in two different cells and the provide the reference to those cells in our formula. To make the formula function appropriately for all the rows, make sure to utilize absolute references for the peripheral cells ($F$1 and $F$2 in this case):
Now we will apply formula in cell C2.
=IF(AND(B2>=$F$1, B2<=$F$2), “X”, “”)
Excel IF AND Function – Example #4
Employing a similar formula as in the above example, it is possible to check if a date lies within a specific range.
As an example, we will flag all the dates between 30-Sep-2018 and 10-Sep-2018, including the peripheral dates. So here we see that we are faced with a unique problem that the dates can’t be directly supplied to the logical tests. To make the dates Excel-understandable, we will need to enclose them inside the DATEVALUE function, as shown below:
=IF(AND(B2>=DATEVALUE(“10-Sep-2018”), B2<=DATEVALUE(“30-Sep-2018”)), “X”, “”)
It is also possible to simply enter the To and From dates in the cells ($F$2 and $F$1 in our example) and then pull them from their source cells by employing the IF AND formula:
=IF(AND(B2>=$F$1, B2<=$F$2), “X”, “”)
Excel IF AND Function – Example #5
Other than returning values that are predefined, the IF AND function are also capable of performing different calculations based on if the mentioned conditions are FALSE or TRUE.
We have a list of students and their test scores as percentages. We now need to grade them. The percentage to grade table is provided below.
As we can see from the table above, if the percentage is above 90% then the associated grade for that score is A+. If the percentage scored is between 85% and 90%, then the grade is A. If the percentage is between 80% and 85% then the grade is B+. If the percentage is between 75% and 80% then the grade is B. If the percentage is between 70% and 75% then the grade is C+. If the percentage is between 60% to 70% then the grade is C. If the percentage is between 50% to 60% then the grade is D+. If the percentage is between 40% to 50% then the grade is D. And lastly, if the percentage is below 40%, then the grade is F.
Now the list of students and their percentage is shown below:
The formula we shall use is:
=IF(C2>”90″%,”A+”,IF(AND(C2>”85″%,C2<=”90″%),”A”,IF(AND(C2>=”80″%,C2<=”85″%),”B+”,IF(AND(C2>=”75″%,C2<=”80″%),”B”,IF(AND(C2>=”70″%,C2<=”75″%),”C+”,IF(AND(C2>=”60″%,C2<=”70″%),”C”,IF(AND(C2>=”50″%,C2<=”60″%),”D+”,IF(AND(C2>=”40″%,C2<=”50″%),”D”,”F”))))))))
We have made use of Nested IF and also used several AND in conjunction with them.
IF function evaluates a condition supplied in its argument and based on the result (either TRUE or FALSE), returns one output, or another.
The formula of IF is as follows:
Here Logical_Test is the condition that will get evaluated. Based on the outcome, either Value_If_True will be returned or else Value_If_False will be returned.
In the first case i.e. for Student1, we see that the percentage is 74%, thus the result returned as per the percentage-to-grade table shown above.
Similarly, for Student 2, we see that the percentage is 76%, thus the result returned as per the percentage-to-grade table shown above, will be B, since percentage between >75% and 80% get grade B.
By dragging the formula applied in cell D2, other students also get graded similarly.
Things to Remember About IF AND Function in Excel
- The IF AND function has a limit and can check up to 255 conditions. This is of course only applicable for Excel 2007 and higher. For previous versions of Excel, the AND function was limited to only 30 conditions.
- IF AND function can handle logical arguments and works even with numbers. But it can’t handle strings. If the arguments provided are characters, then AND returns a #VALUE! error.
- While working with large sets of data, there are high chances that we may be required to check a few different sets of different AND conditions at a time. At times like these, we need to use the nested form of the IF AND formula:
IF(AND(…), output1, IF(AND(…), output2, IF(AND(…), output3,IF(AND(…) output4)))
Recommended Articles
This is a guide to IF AND Function in Excel. Here we discuss how to use IF AND Function in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
