Excel IF function with multiple conditions (2023)

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

in our first partExcel IF tutorial, we looked at how to create a simple IF statement with a condition for text, numbers, dates, spaces, and non-spaces. However, for robust data analysis you will often need to evaluate multiple conditions simultaneously. The following type of example will show you the most efficient way to do this.

How to use the IF function with multiple conditions

Basically, there are two typesIF types with multiple conditions based on AND/OR logic.Therefore, in a logical IF test, you should use one of the following functions:

with operation- returns TRUE ifall conditionssatisfied? otherwise false.
function- returns TRUE ifany individual situationsatisfied? otherwise false.

To better illustrate this, let's study some examples of formulas from real life.

Excel IF Statement with Multiple Conditions (AND Logic)

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

I (I (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, then returnvalue_if_true; otherwise return itvalue_if_false.

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

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

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

=IF(AND(B2>50, B2>50), "Prolaz", "Pad")

It's simple, isn't it? The following screenshot proves that the Excel IF/AND formula is correct:

In a similar way, you can use the Excel IF function with moretext state.

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

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

Important tip!And function checkall conditions, even if already tested has the value FALSE. This behavior is somewhat unusual because in most programming languages metaconditions are not checked if any previous test returned FALSE.

In fact, apparently correct IF statements can be incorrect because of this peculiarity. For example, the formula below will return #DIV/0! (Division by zero error) If cell A2 is equal to 0:

=IF(AND(A2<>0, (1/A2)>0,5), "Dobro", "Loše")

To avoid this, you should use nested IF functions:

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

For more information seeIF AND formulas in Excel.

Excel IF function (or logic) with multiple conditions

do one thing ifany conditionSatisfy, else do something else, using a combination of the IF and OR functions:

me for (Condition 1,Condition 2, …), value_if_true, value_if_false)

IF s više naredbi AND & OR

If your task requires evaluating multiple sets of conditions, you must use both the AND and OR functions.

In our table example, let's say you have the following criteria to check your test results:

Condition 1: test 1>50 and test 2>50
Condition 2: exam1>40 and exam2>60

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

At first glance, the formula may seem a bit difficult, but it's not! Just express each of the above conditions as an AND statement and put them inside an OR function (since both conditions don't have to be true, one of them is enough):

ili (sa (B2>50, C2>50), sa (B2>40, C2>60)

Then use the OR function to do a logical IF test and return the queryvalue_if_trueIvalue_if_falsevalue. As a result, you will have the following IF formula with multiple AND/OR conditions:

=IF(OR(AND(B2>50, C2>50), AND(B2>40, C2>60), "passed", "failed")

The following screenshot shows that we completed the formula correctly:

Of course, you are not limited to using only two AND/OR functions in the IF formula. You can use them as much as your business logic requires, provided that:

In Excel 2007 and later versions, the number of parameters does not exceed 255 and the total length of the IF formula does not exceed 8192 characters.
In Excel 2003 and earlier versions, the number of parameters does not exceed 30, and the total length of the IF formula does not exceed 1024 characters.

Nested IF statements check multiple logical tests

You can nest multiple functions inside another function if you want to evaluate multiple logic tests on a single type. Such a function is calledNested IF functions.They are especially useful when you want to return a different value based on the result of a logical test.

Here's a typical example: suppose you want to limit a student's grades to "Well","satisfied"I"Poor"Based on the following results:

Good: over 60 (>=60)
Satisfactory: between 40 and 60 (>40 and <60)
Bad: below 40 (<=40)

Before writing the formulas, consider the order of the functions to be embedded. Excel will calculate the logical tests in the order they appear in the formula. Once a condition evaluates to TRUE, subsequent conditions are not checked, meaning that the formula stops after the first TRUE result.

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

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

You can, of course, integrate many functions (up to 64 in modern versions) if you wish.

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

Excel IF field type with multiple conditions

Another way to test multiple Excel IF conditions is to usefield type.

Use an asterisk to evaluate conditions with logical AND:

I(Condition 1) * (Condition 2) * …, value_if_true, value_if_false)

To test a condition using OR logic, use the plus sign:

I(Condition 1) + (Condition 2) + …, value_if_true, value_if_false)

To correctly fill in the field types, pressCtrl + Shift + Enterkeys together. In Excel 365 and Excel 2021, for supportdynamic table.

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

=IF((B2>50) * (C2>50), "passed", "failed")

In my Excel 365 the normal formula works fine (as you can see in the screenshot above). In Excel 2019 and earlier versions, remember to useCtrl + Shift + Entershortest way.

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

=IF((B2>50) + (C2>50), "passed", "failed")

Using IF with other functions

This section describes how to use IF with other Excel functions and what benefits it can provide.

Example 1. If a #N/A error occurs in VLOOKUP

When VLOOKUP or other lookup functions can't find something, they return a #N/A error. To make your table look better, you can return null, blank, or some text if #N/A. To do this, use this general formula:

if(ISNA(VLOOKUP(...)),value_if_on, VLOOKUP(…))

For example:

If #N/A returns 0:

If the requested value is not found in E1, the formula returns null.

=IF(ISNA(VLOOKUP(E1, A2:B10, 2,FALSE )), 0, VLOOKUP(E1, A2:B10, 2, FALSE))

If #N/A returns empty:

If the requested value is not found, the formula returns nothing (an empty string).

=IF(ISNA(VLOOKUP(E1, A2:B10, 2,FALSE )), "", VLOOKUP(E1, A2:B10, 2, FALSE))

If #N/A returns specific text:

The formula returns the specified text if the requested value is not found.

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

For more examples of formulas seeVLOOKUP using the IF statement in Excel.

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

To sum cell values based on certain criteria, Excel offersSumifISUMIFSMode.

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 would be:

=IF(SUM(B2:C2)>130, "Good", IF(SUM(B2:C2)>110, "Fair", "Bad"))

If the sum is greater than 130, the result is "good", if it is higher than 110 - "satisfactory", if it is 110 or lower - "bad".

You can install in a similar wayAveragePlaying with the logical IF test, returning different labels based on the average result:

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

Assuming the total is in column D, you can useupper limitIminimum priceMode:

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

To put both labels in one column, put the above function inside another function:

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

You can also use IF with your custom functions. For example, you can use it withGetCellColor 或 GetCellFontColorReturns different results depending on the cell color.

In addition, Excel also provides many functions to calculate data based on conditions. For detailed examples of formulas, see the following guides:

COUNTIF- Count the cells that meet the criteria
count- Count cells with multiple criteria
Sumif- Conditional addition of cells
SUMIFS- Sum cells with multiple conditions

Primjer 3. IF s ISNUMBER, ISTEXT i ISBLANK

To recognize text, numbers, and blank cells, Microsoft Excel offers special functions such as ISTEXT, ISNUMBER, and ISBLANK. You can identify different data types at the same time by placing them in the logical test of three nested IF statements:

=IF(ISTEXT(A2), "Tekst", IF(ISNUMBER(A2), "Broj", IF(ISBLANK(A2), "Prazno", "")))

Example 4. IF I JOIN

To print the result of an IF and some text in a cell, use thisin a rowtheComcat(in Excel 2016 - 365) works with IF. For example:

=CONCATENATE("Your Performance", IF(B1>100,"Excellent!", IF(B1>50, "Good", "Bad"))))

=CONCAT("Your Performance", IF(B1>100,"Excellent!", IF(B1>50, "Good", "Bad"))

Check out the screenshot below, no need to explain what the guy is doing:

type IF ISERROR / ISNA in Excel

Modern versions of Excel have special features for logging errors and replacing them with another calculated or predefined value -error(in Excel 2007 and later) iIFNA(in Excel 2013 and later). In older versions of Excel, you could useif it is not correctIin case thatCombine instead.

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 focus on #N/A errors.

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

=IF(ISGREŠKA(A2/B2), "N/A", A2/B2)

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!

Download the practice book

Excel IF multiple conditions - example(.xlsx file)

Maybe you're right. . . I'm interested in

Nested IF Statements: Examples, Best Practices, and Workarounds
Excel IF statement between two numbers or dates
Check if two or more cells are equal