IF AND in Excel

If and And are the two most different functions which are usually nested together, in a simple If function we can only test a single criteria but if we use the logical AND function we can test more than one criteria and give output based on the criteria, they are used as follows =IF ( Condition 1 AND Condition 2, Value if true , Value if False).

IF AND in Excel Formula (Table of Contents)

• Formula
• How to Use?

IF AND in Excel Formula

The usage of IF AND formula in excel is widely applied as the combination of the two logical functions enables the user to evaluate multiple conditions using AND functions and based on the outcome of AND function the IF function returns the true or false value respectively. The IF formula in excel is the logical formula used to test and compare the condition expressed with the expected value by returning the desired result if the condition is either TRUE or FALSE.

The AND function, on the other hand, is also known as a logical function, this formula is used to test multiple criteria wherein it returns TRUE if all the conditions mentioned are satisfied or else returns FALSE if either of the one criteria is false. AND function can also be used with IF formula to return the desired result.

Explanation of IF AND Formula in Excel

The below syntax will help you understand the IF AND Formula in Excel:-

#1 – AND Formula

• logical 1:- This is a mandatory argument to be entered. Here the first condition to be evaluated must be entered.
• [logical 2]:- This is an optional argument. Here the second condition to be tested should be entered. Similarly, we can enter multiple conditions by separating them by a comma“, ”.

#2 – IF Formula

• Logical_test:- Here the argument to be entered is mandatory. The condition to be evaluated needs to be entered in this argument. In case of IF AND combination formula, the AND function should be entered with the desired number of conditions.
• Value_if_true:- The IF formula returns the true value if the condition entered in the logical_test argument is satisfied. In IF AND function, if all the conditions in AND formula are true then IF function will return the TRUE value which is entered in the value_if_true argument.
• Value_if_false:- If all the conditions in AND function is false then the IF function returns the false value given in the value_if_false argument.

#3 – IF AND Formula in Excel

The syntax for IF AND formula in excel is shown below:-

How to Use IF AND Formula in Excel?

Below are some examples of how to use IF AND Formula in Excel.

IF AND Formula Excel Example #1

Consider the following table to understand the functioning of IF AND function.

The table has a comparative analysis for apartments to be made on the basis of the age of the building and the type of society. In this example, we would be using the combination of less than equal to excel operator and the equal to text functions in the condition to be demonstrated for IF AND function.

The function used to perform the check is

=IF(AND(B2<=2,C2=”Gated”),”Consider”,””)

Below is the screenshot of the condition applied to the table to perform the evaluation.

Press Enter to get the answer.

Drag the formula to find the answer for all apartments.

Explanation:- Based on the above example let us perform a check to evaluate the apartments for cell B2 less than and equal to 2 years and type of society as a gated community. So the IF AND function will be doing one of the following:-

• If both the arguments entered in AND function is TRUE then the IF function will mark the apartment to be considered.
• If either of the arguments is found to be FALSE or both the arguments entered in AND function is FALSE then the IF function will return a blank string.

IF AND Formula Excel Example #2

The IF AND function can also perform calculations based on whether AND function is TRUE or FALSE apart from returning only the predefined text strings.

Let us consider the below data table to understand this approach for calculating the employee hike based on the number of orders received and performance as the parameters for AND condition.

The syntax entered for IF AND function is as follows.

=IF(AND(B2>=200,C2=”A”),D2*10%,D2*5%)

Press Enter and see the final output of the above example.

Drag the formula to find the bonus of all employee.

Explanation:- The criteria to calculate bonus is done if the condition for a number of orders received is greater than equal to 300 and the performance is “A”. Based on the results the IF  function does the following:-

• If both the conditions are satisfied then AND function is TRUE then the bonus received is calculated as salary multiplied by 10%.
• If either of the one or both the conditions are found to be FALSE by AND function then the bonus is calculated as salary multiplied by 5%.

IF AND Formula Excel Example #3

As we have seen the above two examples had only two criteria’s to test and evaluate the AND conditions, but nothing is stopping you to use multiple arguments or conditions to test them for TRUE or FALSE. Let us evaluate three criteria’s in AND function in the below data table.

Referring to the data table we have five stocks with their respective financial ratios such as ROCE, ROE, Debt to equity and PE ratio. Using these parameters we will be analyzing the stocks which will have the best investment horizon for better growth. So let us apply the condition to arrive at the result.

As can be seen in the above table we have the stocks and their respective parameter details which we will be using to test the condition to invest in the suitable stocks.

The syntax used in the below table is:-

=IF(AND(B2>18%,C2>20%,D2<2,E2<30%),”Invest”,””)

Press Enter and see the final output of the above example.

Drag the formula to find the Investment Criteria.

In the above data table, the AND function tests for the parameters using the operators and the resulting output generated by the IF formula is as follows:-

• If all the four criteria’s mentioned in AND function are tested and satisfied then IF function will be returning “Invest” text string.
• If either one or more of the four conditions or all the four conditions fail to satisfy the AND function then the IF function will be returning empty strings.

Things to Remember

1. The IF AND function in combination do not differentiate towards case insensitive texts that are uppercase and lower case letters.
2. The AND function can be used to evaluate up to 255 conditions for TRUE or FALSE and the total formula length must not 8192 characters.
3. Text values or blank cells are given as an argument to test the conditions in AND function would be ignored.
4. The AND function will return “#VALUE!” if there is no logical output found while evaluating the conditions.

Recommended Articles

This has been a guide to IF AND in Excel. Here we discuss how to use the two logical functions IF Formula combined with AND in excel along examples and downloadable excel sheet. You may also look at these useful functions in excel –

• Excel Text SUMIF Examples
• SUM Shortcut in Excel
• Excel SUMIF with Multiple Criteria
• Nested If Condition in Excel
• False in Excel
• Use Logical Test in Excel
• List of Excel Functions
• List of Basic Excel Formulas
• Use Nested IF in Excel