Excel IF function with multiple conditions (2023)

The tutorial shows how to create multiple IF statements in Excel with AND and OR logic. You'll also learn how to use IF with other Excel functions.

In the first part of ourExcel SI Tutorialwe saw how to create a simple IF statement with a condition for text, numbers, dates, spaces and non-spaces. However, for effective data analysis, you often need to test multiple conditions simultaneously. The formula examples below will show you the most efficient ways to do this.

How to use the IF function with multiple conditions

Basically there are two typesMulti-criteria IF type based on AND/OR logic. Therefore, in the logical test of the IF formula, you must use one of the following functions:

and operation- returns TRUE ifall conditionsdid they meet otherwise FALSE.
Function- returns TRUE ifany conditionit has been completed; otherwise FALSE.

To better illustrate this point, let's look at some examples of real-life formulas.

Excel IF Statement with Multiple Conditions (Boolean AND)

The general Excel IF formula with two or more conditions is as follows:

YES AND(condition 1,condition 2, ...), value_if_true, value_if_false)

Translated into human language, the formula says: If condition 1 is true AND condition 2 is true, returnvalue_if_true; otherwise come backvalue_if_false.

Suppose you have a table containing the scores for two exams in columns B and C. To pass the final exam, a student must have both scores greater than 50.

For a logical test, use the following AND statement: AND(B2>50, C2>50)

If both conditions are met, the formula will return "Success". if any condition is false - "Failure".

=IF(AND(B2>50, B2>50), "Success", "Failure")

Easy, right? The following screenshot shows the Excel IF/AND formula working correctly:

Similarly, you can use Excel's IF function with manytext conditions.

For example, to output "Good" if both B2 and C2 are greater than 50 and "Bad" otherwise, the formula is:

=IF(AND(B2="pass", C2="pass"), "Good!", "Bad")

Important note!The AND function checksall conditions, even if they have already been checked and evaluated as FALSE. This behavior is somewhat unusual because in most programming languages metaconditions are not checked if any of the previous tests returned FALSE.

In practice, a seemingly valid IF statement can cause an error due to this peculiarity. For example, the formula below will return #DIV/0. ("divide by zero" error) if cell A2 equals 0:

=IF(AND(A2<>0,(1/A2)>0.5),"Good", "Bad")

To avoid this, you should use a nested IF function:

=SI(A2<>0,SI((1/A2)>0.5, "Good", "Dry"), "Dry")

For more information, seeIF AND Formula in Excel.

Excel IF function with multiple conditions (OR logic)

do one thing ifany conditionis true, otherwise do something else, use this combination of IF and OR functions:

I FOR(condition 1,condition 2, ...), value_if_true, value_if_false)

IF with multiple AND and OR clauses

If your task requires evaluating multiple sets of conditions, you'll need to use AND and OR at the same time.

Let's say that in our example table you have the following criteria to verify test results:

Condition 1: test1>50 and test2>50
Condition 2: exam1>40 and exam2>60

If any of the conditions are met, the final exam is considered successful.

At first glance, the formula looks a bit complicated, but it really isn't! Simply express each of the above conditions as an AND statement and place them in an OR function (since both conditions do not need to be met, one is sufficient):

O(ORAZ(B2>50, C2>50), Y(B2>40, C2>60)

Then use the OR function for a logical IF test and specify the desired valuevalue_if_trueIvalue_if_falsevalues. As a result, you get the following IF formula with multiple AND/OR conditions:

=IF(OR(AND(B2>50, C2>50), AND(B2>40, C2>60), "Success", "Fail")

The following screenshot shows that we have executed the formula correctly:

Of course, you are not limited to using only two AND/OR functions in your IF formulas. You can use whatever your business logic requires, as long as:

In Excel 2007 and later, it has no more than 255 arguments and the total length of the IF formula does not exceed 8192 characters.
In Excel 2003 and earlier, there are no more than 30 arguments and the total length of the IF formula does not exceed 1024 characters.

Nested IF statement to control multiple logical tests

If you want to evaluate multiple logic tests on a formula, you can nest multiple functions inside each other. Such functions are callednested IF functions. They are especially useful when you want to return different values based on the results of logical tests.

Here's a typical example: Let's say you want to rank student performance as "GOOD","Satisfying"I"Weak"based on the following results:

Good: 60 or more (>=60)
Normal: 40 to 60 (>40 to <60)
Weak: 40 or less (<=40)

Before writing the formula, consider the order of functions you plan to incorporate. Excel will evaluate the logical tests in the order they appear in the formula. When a condition evaluates to TRUE, subsequent conditions are not checked, meaning that the formula stops after the first TRUE result.

In our case, the functions are ordered from largest to smallest:

=IF(B2>=60, "Good", IF(B2>40, "Success", "Bad"))

You can of course fit more functions if needed (up to 64 in modern versions).

For more information, seeHow to use multiple nested IF statements in Excel.

Excel IF table type with multiple conditions

Another way to test multiple conditions of Excel's IF function is to use a methodmatrix type.

To evaluate conditions using AND logic, use an asterisk:

AND(condition 1) * (condition 2) * ..., value_if_true, value_if_false)

To test conditions with OR logic, use the plus sign:

AND(condition 1) + (condition 2) + …, value_if_true, value_if_false)

To fill in the table type correctly, press the keyCtrl + Shift + Enterkeys together. In Excel 365 and Excel 2021, this also works as a regular formula for compatibilitydynamic arrays.

For example, to get "Success" if both B2 and C2 are greater than 50, the formula is:

=IF((B2>50) * (C2>50), "Success", "Failure")

In my Excel 365 the normal formula works fine (as you can see in the screenshots above). In Excel 2019 and earlier, make sure to set it as a table type using a methodCtrl + Shift + Enterabbreviation.

To evaluate multiple conditions using OR logic, the formula is as follows:

=IF((B2>50) + (C2>50), "Success", "Failure")

Using IF with other functions

This section explains how to use the IF function in conjunction with other Excel functions and the benefits it provides.

Example 1. If the #N/A error in VLOOKUP

When VLOOKUP or another lookup function can't find something, it returns the #N/A error. To make your tables look better, you can return null, empty text, or specific text if #N/A. To do this, use this general formula:

IF(ISNA(VLOOKUP(…)),value_if_on, VERTICAL SEARCH (…))

For example:

If #N/A returns 0:

If the value you are looking for is not found in E1, the formula will return null.

=IF(ISON(BUSCARV(E1,A2:B10,2,FALSE)), 0,BUSCARV(E1,A2:B10,2,FALSE))

If #N/A returns empty:

If the value you are looking for is not found, the formula returns nothing (an empty string).

=IF(ISON(BUSCARV(E1,A2:B10,2,FALSE)), "",BUSCARV(E1,A2:B10,2,FALSE))

If #N/A returns the specified text:

If the search value is not found, the formula returns the specified text.

=IF(ISON(VLOOKUP(E1,A2:B10,2,FALSE)), "Not Found", VLOOKUP(E1,A2:B10,2,FALSE))

For more examples of formulas, seeVLOOKUP with IF statement in Excel.

Example 2. IF with SUM, AVERAGE, MIN and MAX functions

To sum cell values based on certain criteria, Excel providesSUMAR.SIIQUANTITIESfunctions.

In some cases, your business logic may need to include the SUM function in the IF logic test. For example, to return different text labels based on the sum of the values in B2 and C2, the formula is:

=IF(SUM(B2:C2)>130, "Καλό", IF(SUM(B2:C2)>110, "Successful", "Bad"))

If the sum is greater than 130, the result is "good". if it is over 110 - "fair", if it is 110 or less - "poor".

You can embed a file in a similar wayAVERAGEperform a logical IF test and return different labels based on the average score:

=IF(AVERAGE(B2:C2)>65, "Good", IF(AVERAGE(B2:C2)>55, "Satisfactory", "Bad"))

Assuming the total score is in column D, you can determine the highest and lowest value withMACHINERYIMY.Functions:

=IF(D2=MAX($D$2:$D$10), "High Score", "")

To have both labels in one column, nest the above functions inside each other:

=IF(D2=MAX($D$2:$D$10), "Best Score", IF(D2=MIN($D$2:$D$10), "Worst Score", ""))

Similarly, you can use IF in conjunction with custom functions. For example, you can combine it withGrease GetCellColor GetCellFontColorto return different results based on cell color.

In addition, Excel provides a number of functions to calculate data based on conditions. For detailed examples of formulas, see the following tutorials:

METERS YES- count the cells that meet the condition
ACCOUNTANTS- cell counting with multiple criteria
SUMAR.SI- conditional sum cells
QUANTITIES- Sum cells with multiple criteria

Example 3. IF with ISNUMBER, ITEXT and ISBLANK

To detect blank text, numbers, and cells, Microsoft Excel provides special functions such as ISTEXT, ISNUMBER, and ISBLANK. By putting them in logical tests of three nested IF statements, you can identify all the different data types in one go:

=IF(ISTEXT(A2), "Text", IF(ISNUMBER(A2), "Number", IF(ISBLANK(A2), "Blank", "")))

Example 4. IF and JOIN

To print the IF result and some text in a cell, use the methodTRUSSlubricantKONKAT(in Excel 2016 - 365) and IF work together. For example:

=CONCATENATE("You did it", IF(B1>100,"great!", IF(B1>50, "good", "bad")))

=CONCAT("You won", IF(B1>100,"great!", IF(B1>50, "good", "bad")))

Looking at the screenshot below, you'll need almost no explanation of what the guy is doing:

The IF IS ERROR / ISNA formula in Excel

Modern versions of Excel have special functions for detecting errors and replacing them with another calculation or a predefined value:IF ERROR(in Excel 2007 and later) andIFNA(in Excel 2013 and later). In older versions of Excel, you can use an extensionIF ERRORIYES THAT'S ITcombinations in place.

The difference is that IFERROR and ISERROR handle all possible Excel errors, including #VALUE!, #N/A, #NAME?, #REF!, #NUM!, #DIV/0! and #NULL!. While IFNA and ISNA only specialize in #N/A errors.

For example, to replace the division-by-zero error (#DIV/0!) with your own text, you can use the following formula:

=SI(ESERROR(A2/B2), "N/A", A2/B2)

And that's all I have to say about using the IF function in Excel. Thanks for reading and we hope to see you on our blog next week!

downloadable workbook

Excel IF multiple criteria - examples(plik.xlsx)