How one can insert Excel If Statement with Multiple Conditions?

It was well known that to perform a powerful or complicated data analysis an individual must needs to test various conditions at a single point at a particular time. The data analysis also requires logical tests within these multiple conditions. And for the purpose of achieving the above, an individual must perform an Excel if statement with multiple conditions.

This tutorial shows how we can create multiple IF statements in Microsoft Excel with AND and OR logic. Also, we will be learning how to make use of the IF function together with other Microsoft Excel functions.

1. How one can make use of the Excel IF function with the help of the multiple conditions?
   1. Use of Multiple AND conditions.
   2. Use of Multiple OR conditions.
   3. Use of AND and OR Statements in a single formula.
2. How one can make use of the multiple nested IF statements in Microsoft Excel?
3. How to insert IF functions in array formulas in Microsoft Excel?
4. How to insert the IF function together with other Microsoft Excel functions?
   1. Use the IF function with the help of the VLOOKUP function.
   2. Use the IF function with the help of SUM, AVERAGE, MIN and MAX.
   3. Use of IF and CONCATENATE.

How one can make use of the Excel IF function with the help of the multiple conditions?

Basically in Microsoft Excel there are two types of the IF formula with multiple criteria based on the AND / OR logic. Consequently, in the logical test of our IF formula, we should make use of the single function from the below-mentioned ones at a time.

AND FUNCTION: If we perform the logical test based on AND function, then Microsoft Excel will give us "TRUE" as an output in every condition; otherwise, it will return "FALSE" as an output.

OR FUNCTION: If we use the OR condition for the given logical test, then Microsoft Excel will provide an outcome as "TRUE" if any situation gets matched; otherwise, it will return "FALSE" as an outcome.

And for a better illustration of the above point, we will be investigating it with some real-life formulas examples.

Excel IF statement with multiple conditions (AND logic)

The generic formula of Microsoft Excel IF functions having two or more conditions are as follows:

We are having a table which is primarily listing out the scores of the two tests in column and to pass the final exam, a student needs to scores greater than 50.

So for the logical test, we are making use of the AND statement:

And if both the conditions are TRUE, then in that case the used formula will return "Pass"; if any condition is false, it will return output as "Fail".

Moreover, in the same way we can make use of the Microsoft Excel IF function with the help of the multiple text condition. And if both the column B2 and C2 are greater than 50, then we should get an output as "GOOD" otherwise ", BAD" the formula which can be effectively used to achieve this are as follows:

Microsoft Excel IF function with multiple conditions (OR logic)

Moves on performing an individual thing if any condition is met otherwise moves on to performing something else by using the combination of IF and OR functions in Excel effectively:

So, now we are making use of the OR function instead of AND in the same example and will see the result:

If any of the students who have gained more than 50 points in either exam will get a "Pass" in column D.

Use of IF with multiple AND & OR statements in Microsoft Excel

If in case, our task requires the evaluation of several sets of multiple conditions then in we need to utilise both AND and OR functions at a particular instance of time.

In our chosen sample table, we will be assuming that we are incorporated with following criteria in order to checking the exam results:

Condition 1: exam1>50 and exam2>50

Condition 2: exam1>40 and exam2>60

And if either of the following conditions is met, the final exam is passed.

Then, we will use the OR function for the logical test of IF and supply the desired value_if_true and value_if_false values. As a result, we will be getting the following IF formula with multiple AND / OR conditions as well:

How to make use of the multiple nested IF statements in Microsoft Excel?

Let us suppose that, there is a scenario in which we want to evaluate the multiple logical tests within a given formula. In that case, we can nest the several functions one into the other such functions are known to be the "nested IF functions".

They are helpful when we want to return different values depending on the various logical test results.

• For example: Let us assume that we want to qualify the particular student's achievements as "Good", "Satisfactory", as well, as " Poor" depending upon the obtained scores:

• Good: Scores are 60 or more (>=60).
• Satisfactory: Scores between 40 and 60 (>40 and <60).
• Poor: Scores are either 40 or less (<=40).

So before moving on writing a formula, we will first consider the order of functions which we are going to nest. And Microsoft Excel will eventually evaluate the logical tests in the order they might appear in the respective formula. Once the condition evaluates to TRUE, the following conditions will not get tested, which means that the formula will stop just after we encounter the First TRUE result.

As in our case, the functions are arranged from most significant to most minor, respectively:

How to insert IF functions in array formulas in Microsoft Excel?

The other methods that can be used to get an Excel IF to test out the multiple conditions are with the help of the "Array formula."

And to evaluate the given conditions with the AND logic, we can also use the asterisk symbol.

And further on to test conditions with the OR logic, we will make use of the plus sign:

We will be pressing the Ctrl + Shift + Enter keys to complete an array formula correctly.

• For example, The condition to get "Pass" if both B2 and C2 are greater than 50, so the formula is:

And to evaluate multiple conditions with the OR logic, we will make use of the below-mentioned formula:

How can one insert the IF functions together with other Microsoft Excel functions?

It will explain how to make use of the IF function in combination with other Microsoft Excel functions.

# Example 1: If #N/A error in VLOOKUP

When VLOOKUP function cannot find something, then it will return out #N/A error, and to make our tables look attractive, we can return zero, blank, or specific text if #N/A so for this, we will make use of this generic formula:

For example:

The formula returns the specific text if the lookup value is not found.

# Example 2: IF with SUM, AVERAGE, MIN and MAX functions in Microsoft Excel

If we want to sum out the cell values, which are based on specific criteria Microsoft Excel will provide the SUMIF and SUMIFS functions respectively:

And in most situations, our business logic may require including of the SUM function in the logical test of IF.

• For example, if we want to return out a different text label depending upon the sum of the values in column B2 and C2.

FORMULA:

And if in case, the given sum is more significant than the value 130 it will display the result as "good"; and if it is greater than the value 110 it will return the result as "satisfactory', and if it is 110 or lower - "poor".

And in the same manner, we can easily embed the AVERAGE function in the logical test of IF and return different labels that are primarily based on the average score:

Let us assume that the total score is present in column D, in which we can effectively identify the highest as well as the lowest values with the help of the MAX and MIN functions.

And to get both the labels in one single column, we can nest the above functions one into another respectively:

#Example 3: Use of IF and CONCATENATE in Microsoft Excel

If we want to output the result of IF and some of the text into one cell, we can make use of the CONCATENATE as well as the IF functions.

For example: