How to Combine IF with AND in Excel

In Excel, the IF function is used to perform a logical test and return one value if the test is true, and another value if the test is false. For example, the formula =IF(A1>10,"Yes","No") will check if the value in cell A1 is greater than 10, and return “Yes” if true, and “No” if false.

But what if you want to check more than one condition at the same time? For example, what if you want to check if the value in cell A1 is between 10 and 20, and the value in cell B1 is “Yes”? In this case, you can use the AND function to combine multiple logical tests into one. The AND function returns TRUE if all the arguments are true, and FALSE if any of the arguments are false. For example, the formula =AND(A1>10,A1<20,B1="Yes") will return TRUE if all the three conditions are met, and FALSE otherwise.

To combine the IF and AND functions, you can use the AND function as the logical test argument of the IF function. For example, the formula =IF(AND(A1>10,A1<20,B1="Yes"),"Pass","Fail") will return “Pass” if the value in cell A1 is between 10 and 20, and the value in cell B1 is “Yes”, and “Fail” otherwise.

Procedure

To combine IF with AND in Excel, follow these steps:

1. Select the cell where you want to enter the formula.
2. Type =IF( and then type the logical test using the AND function. For example, =IF(AND(A1>10,A1<20,B1="Yes"),
3. Type the value that you want to return if the logical test is true, followed by a comma. For example, =IF(AND(A1>10,A1<20,B1="Yes"),"Pass",
4. Type the value that you want to return if the logical test is false, followed by a closing parenthesis. For example, =IF(AND(A1>10,A1<20,B1="Yes"),"Pass","Fail")
5. Press Enter to complete the formula.

Example

Let’s say you have a table of students’ scores and attendance, and you want to assign a grade based on the following criteria:

• If the score is greater than or equal to 80, and the attendance is “Yes”, the grade is “A”.
• If the score is between 60 and 79, and the attendance is “Yes”, the grade is “B”.
• If the score is between 40 and 59, and the attendance is “Yes”, the grade is “C”.
• If the score is less than 40, or the attendance is “No”, the grade is “F”.

The table looks like this:

To assign the grade, you can use the following formula in cell D2 and copy it down to the other cells:

=IF(AND(B2>=80,C2="Yes"),"A",IF(AND(B2>=60,B2<80,C2="Yes"),"B",IF(AND(B2>=40,B2<60,C2="Yes"),"C","F")))

The formula works by nesting multiple IF functions inside each other, and using the AND function to check the score and attendance conditions. The result is:

Other Approaches

There are other ways to combine IF with AND in Excel, such as using the IFS function, the SWITCH function, or a lookup table. Here are some examples of how to use these approaches:

• Using the IFS function: The IFS function allows you to specify multiple logical tests and values in pairs, and returns the first value that matches a true test. For example, the formula =IFS(AND(B2>=80,C2="Yes"),"A",AND(B2>=60,B2<80,C2="Yes"),"B",AND(B2>=40,B2<60,C2="Yes"),"C",TRUE,"F") will return the same result as the nested IF formula above.
• Using the SWITCH function: The SWITCH function allows you to evaluate an expression and return a value based on a list of possible matches. For example, the formula =SWITCH(AND(B2>=80,C2="Yes"),"A",AND(B2>=60,B2<80,C2="Yes"),"B",AND(B2>=40,B2<60,C2="Yes"),"C","F") will return the same result as the nested IF formula above.
• Using a lookup table: A lookup table is a range of cells that contains the criteria and the corresponding values. For example, you can create a lookup table like this:

Then, you can use the VLOOKUP function or the INDEX-MATCH function to look up the grade based on the score and attendance. For example, the formula =VLOOKUP(AND(B2>=80,C2="Yes"),$G$2:$I$5,3,FALSE) will return the same result as the nested IF formula above, where $G$2:$I$5 is the lookup table range.