Multiple if statements in Excel (nested IF, AND/OR) with examples (2023)

We use the IF statement in Excel to test a condition and return one value if the condition is met and another value if the condition is not met.

However, when evaluating different conditions in a specific order, we use multiple or nested IF statements to return different results.

This tutorial shows four examples of using nested IF statements in Excel and shows five alternatives to usemultiple IF statements in Excel.

Contents

Common Syntax for Nested IFs (Multiple IFs)

The general syntax for nested IF statements is as follows:

=IF(condition1, value_if_true1, IF(condition2, value_if_true2, IF(condition3, value_if_true3, value_if_true)))

This formula checks the first condition. If true, the first value is returned.

If the first condition is false, the formula goes to the second condition and returns the second value if true.

Each subsequent IF function is integratedvalue_if_falsethe argument of the previous IF function.

This process continues until all conditions are evaluated and the formula returns a final value if none of the conditions are met.

The maximum number of nested IF statements allowed in Excel is 64.

Now consider the following four examples of using nested IF statements in Excel.

Example 1: Use multiple IF statements to set letter grades based on numeric scores

Consider the following dataset that shows the performance of some students on a math test.

Multiple if statements in Excel (nested IF, AND/OR) with examples (1)

We want to use nested IF statements to assign letter grades to students based on their performance.

We apply the following steps:

  1. Select cell C2 and enter the following formula:
=ALS(B2>=90,"A",ALS(B2>=80,"B",ALS(B2>=70,"C",ALS(B2>=60,"D","F")) ))
Multiple if statements in Excel (nested IF, AND/OR) with examples (2)
  1. Click Enter in the cell to get the result of the formula in the cell.
  1. Copy the formula for the remaining cells in the column

Assigned letter grades appear in column C.

Multiple if statements in Excel (nested IF, AND/OR) with examples (3)

Explanation of the formula

=ALS(B2>=90,"A",ALS(B2>=80,"B",ALS(B2>=70,"C",ALS(B2>=60,"D",",F") )))

This formula evaluates the value in cell B2 and assigns "A" if the value is 90 or greater, "B" if the value is between 80 and 89, "C" if the value is between 70 and 79, "D" if the value is between 60 and 69 and "F" if the value is less than 60.

Note that it can be difficult to keep track of which parentheses match which arguments in nested IF functions.

Therefore, when we enter a formula, Excel uses different colors for itbracketsat each level of nested IF functions to make it easier to see which parts of the formula match.

Multiple if statements in Excel (nested IF, AND/OR) with examples (4)
Read also:Use Excel As with multiple condition ranges

Example 2: Use multiple IF statements to calculate commission based on sales volume

Here is a dataset that shows the sales of specific sellers in a specific month.

Multiple if statements in Excel (nested IF, AND/OR) with examples (5)

We want to use multiple IF do statementscalculate a scaled commissionfor sellers based on their sales volume.

We proceed as follows:

  1. Select cell C2 and enter the following formula:
=ALS(B2>=40000, B2*0,14,ALS(B2>=20000,B2*0,12,ALS(B2>=10000,B2*0,105,ALS(B2>0,B2*0,08, 0))))
Multiple if statements in Excel (nested IF, AND/OR) with examples (6)
  1. Press Enter to get the result of the formula.
  2. Double-click or drag the fill handle to copy the formula to the column.

The commission for each seller is shown in column D.

Multiple if statements in Excel (nested IF, AND/OR) with examples (7)

Explanation of the formula

=ALS(B2>=40000, B2*0,14,ALS(B2>=20000,B2*0,12,ALS(B2>=10000,B2*0,105,ALS(B2>0,B2*0,08, 0))))

This formula evaluates the value in cell B2 and then does the following:

See Also
Nested IF in Excel - formula with multiple conditionsFilter using advanced criteria

  • If the value in cell B2 is greater than or equal to 40,000, the number is multiplied by 14% (0.14).
  • If the number in cell B2 is less than 40,000 but greater than or equal to 20,000, the value is multiplied by 12% (0.12).
  • If the number in cell B2 is less than 20,000 but greater than or equal to 10,000, the number is multiplied by 10.5% (0.105).
  • If the value in cell B2 is less than 10,000 but greater than 0 (zero), the number is multiplied by 8% (0.08).
  • If the value in cell B2 is 0 (zero), 0 (zero) is returned.

Example 3: Use multiple IF statements to set a sales score based on the achievement of a sales goal

Below is a set of local sales data for a given technology company in a given year.

Multiple if statements in Excel (nested IF, AND/OR) with examples (8)

We want to use multiple IF statements to assign a sales performance score to each region based on the achievement of the sales goal.

We apply the following steps:

  1. Select cell C2 and enter the following formula:
=IF(B2>500000, "Excellent", IF(B2>400000, "Good", IF(B2>275000, "Average", "Poor")))
Multiple if statements in Excel (nested IF, AND/OR) with examples (9)
  1. Click Enter on the typing bar.
  2. Drag or double-click the fill handle to copy the formula to the column.

Area performance results are listed in column C.

Multiple if statements in Excel (nested IF, AND/OR) with examples (10)

Explanation of the formula

=IF(B2>500000, "Excellent", IF(B2>400000, "Good", IF(B2>275000, "Average", "Poor")))

In this formula, if the sales target in cell B2 is greater than 500,000, the formula returns "Great".

If it is between 400,000 and 500,000, the formula returns "Good."

If it is between 275,000 and 400,000, the formula returns "Average". And if it is less than 275,000, the formula will return "Bad".

Example 4: Use multiple IF statements in Excel to check for errors and return error messages

Suppose we have the following dataset about student performance in English. Some results are less than 0 or greater than 100, and in some cases there are no results.

Multiple if statements in Excel (nested IF, AND/OR) with examples (11)

We want to use nested IF statements to check the results in column B and display error messages in column C if there are no results or if the results are less than 0 or greater than 100.

If the result in column B is valid, we want the formula to return an empty string in column C.

Here are the steps to follow:

  1. Select cell C2 and enter the following formula:
=IF(OR(B2<0;B2>100),"Result out of range",IF(ISBLANK(B2),"Invalid result",""))
Multiple if statements in Excel (nested IF, AND/OR) with examples (12)
  1. Click Enter on the typing bar.
  2. Drag the fill handle to copy the formula down the column.

Error messages appear in column C.

Multiple if statements in Excel (nested IF, AND/OR) with examples (13)

Explanation of the formula

=IF(OR(B2<0,B2>100),"Result Out of Range",IF(ISBLANK(B2),"Invalid Result"))

This formula uses the OR function to check if the result in cell B2 is less than 0 or greater than 100, and if so, returns the "Result out of range" error.

The formula also uses the ISBLANK function to check if cell B2 is empty, and returns an "Invalid Result" error if it is.

If there is no error, the formula returns an empty string, meaning no message is displayed in column B.

Alternatives to using multiple IF statements in Excel

Formulas that use nested IF statements can be difficult to read and manage if you have more than a few conditions to test.

In addition, if we exceed the maximum allowable limit of 64 nested IF statements, we will get an error.

Fortunately, Excel offers alternatives to using nested IF functions, especially when we need to test more than a few conditions.

In this tutorial, we present alternative ways.

Alternative #1: Use IFS

ZIFSThe function checks if one or more conditions are true and returns the value corresponding to the first TRUE.

Before IFS was released in 2018 as part of the Excel 365 update, the only way to test multiple conditions and return the right value in Excel was to use nested IF statements.

However, many IF statements have the disadvantage of leading to unwieldy formulas that are difficult to read and maintain.

In some cases, the IFS function is intended to replace multiple IF functions.

The syntax of the IFS function is simpler and clearer than nested IF statements and can handle up to 127 conditions.

Here is an example:

Consider the following dataset that shows the performance of some students on a math test.

Multiple if statements in Excel (nested IF, AND/OR) with examples (14)

We want to use IFS to assign letter grades to students based on their performance.

We apply the following steps:

  1. Select cell C2 and enter the following formula:
=ALS(B2>=90, "A", B2>=80, "B", B2>=70, "C", B2>=60, "D", B2<60, "F")
Multiple if statements in Excel (nested IF, AND/OR) with examples (15)
  1. Click Enter on the typing bar.
  2. Drag or double-click the fill handle to copy the formula to the column.

The student's letter grades are displayed in column C.

Multiple if statements in Excel (nested IF, AND/OR) with examples (16)

Explanation of the formula

=ALS(B2>=90, "A", B2>=80, "B", B2>=70, "C", B2>=60, "D", B2<60, "F")

This formula checks the number in cell B2 in each condition and returns the corresponding digit letter when the condition is true.

Limitation of IFS functionality

The IF function in Excel is designed to simplify complex nested IF statements.

However, there are cases where an IF function may not completely replace nested IF functions.

One such situation is when you need to calculate or use a condition or set of conditions.

Although the IFS function can return a value or string based on a condition, it cannot perform calculations or operations on that value, such as nested IF statements.

Another situation where IFS may be less useful is when you need to check a set of conditions, not just a specific set.

This is because IFS requires you to specify each condition and its result separately, which can become impractical if you have multiple conditions to test. On the other hand, nested IF statements allow you to test a series of conditions using logical operators such as AND and OR.

The IFS function is a powerful tool for simplifying complex logical tests in Excel.

However, there may be cases where nested IF statements are more appropriate for your needs.

We recommend that you consider both options and choose the one that best suits your specific job requirements.

Alternative #2: Use the NestedIF function

We can use multiple IFS functions in a formula if we have more than one condition to test.

Suppose we have the following dataset with student names and physics test scores in columns A and B.

Multiple if statements in Excel (nested IF, AND/OR) with examples (17)

We want to assign a letter grade to each score and determine if the score is above or below 75, if the score is above or below 75.

Here are the steps to use:

  1. Select cell C2 and enter the following formula
=ALS(B2>=90,"A",B2>=80,"B",B2>=70,"C",B2>=60,"D",B2<60,"F")&" " &IFS(B2>=75"Toegang";B2<75"Błąd")
Multiple if statements in Excel (nested IF, AND/OR) with examples (18)
  1. Click Enter on the typing bar.
  2. Drag or double-click the fill handle to copy the formula to the column.

The student's literal grade and grade designation are listed in column C.

Multiple if statements in Excel (nested IF, AND/OR) with examples (19)

Explanation of the formula

=ALS(B2>=90,"A",B2>=80,"B",B2>=70,"C",B2>=60,"D",B2<60,"F")&" " &IFS(B2>=75,"Kropka",B2<75,"Niepowodzenie")

This formula uses the first IFS function to assign a letter grade based on the score in column A, and the second IFS function to determine the pass/fail score based on the score in column A.

These two IFS functions are combined using the ampersand (&) operator to form a single string representing the letter digit and the pass/fail indicator of each digit.

Alternative #3: Use a combination of SELECT and XMATCH

ZTO CHOOSEThe function selects a value or action from a list of values ​​based on an index number.

ZXMATCHThe function locates and returns the relative position of an element in an array. We can combine these functions into a formula instead of nested IF functions.

Here is an example:

For example, let's say we have the following data set of some students and letter grades on a biology test.

Multiple if statements in Excel (nested IF, AND/OR) with examples (20)

We want to use a formula that combines SELECT and MATCH to assign the appropriate digits in column D to each digit of a letter.

We apply the following steps:

  1. Select cell D2 and enter the following formula:
=KIES(XMATCH(C2;{"F","E","D","C","B","A"};0);0,1,2,3,4,5)
Multiple if statements in Excel (nested IF, AND/OR) with examples (21)
  1. Click Enter on the typing bar.
  2. Drag or double-click the fill handle to copy the formula to the column.

The grades for each student appear in column D.

Multiple if statements in Excel (nested IF, AND/OR) with examples (22)

Explanation of the formula

=KIES(XMATCH(C2;{"F", "E", "D", "C", "B", "A"},0),0,1,2,3,4,5)

This formula uses the XMATCH function to find the digit position of a letter in an array {"F", "E", "D", "C", "B", ""A"} and then uses the CHOOSE function to return the corresponding digits .

Alternative #4: Use VLOOKUP

ZVERTICAL SEARCHThe function searches for a value in the left column of an array, then returns the value in the same row from the specified column.

We can use VLOOKUP function instead of nested IF functions in Excel.

The following is an example of using the VLOOKUP function instead of nested IF functions in Excel:

For example, let's say we have the following data set of some students and letter grades on a biology test.

Multiple if statements in Excel (nested IF, AND/OR) with examples (23)

We want to use VLOOKUP to assign grades to each student's letter grades in column D.

We apply the following steps:

  1. Create a table with numbers and their corresponding numbers in the cell range F1:G7.
Multiple if statements in Excel (nested IF, AND/OR) with examples (24)
  1. In cell D2, enter the following formula:
=VLOOKUP(C2,$F$2:$G$7,2,FALSE)

Attention:Use dollar signs to block the cell range F2:G7.

Multiple if statements in Excel (nested IF, AND/OR) with examples (25)
  1. Click Enter on the typing bar.
  2. Drag or double-click the fill handle to copy the formula to the column.

The grades for each student appear in column D.

Multiple if statements in Excel (nested IF, AND/OR) with examples (26)

Explanation of the formula

=VLOOKUP(C2,$F$2:$G$7,2,FALSE)

This formula uses the VLOOKUP function to search for a number in cell C2 in the table at F2:G7 and returns the corresponding number in the second column (i.e., column G).

The "FALSE" argument requires an exact match.

Alternative #5: Use a user-defined function

If you need to test more than a few conditions, consider creating oneUser-defined modein VBA that can handle multiple conditions.

Here is an example of using VBA code to override nested IF functions in Excel:

Suppose we have the following dataset that shows the sales of specific sellers in a given month.

Multiple if statements in Excel (nested IF, AND/OR) with examples (27)

We want to use a user-defined function to calculate the commission for each salesperson based on the following values:

  • If total sales are less than $10,000, the commission rate is 8%.
  • If total sales are equal to or greater than $10,000 but less than $20,000, the commission rate is 10.5%.
  • If total sales are equal to or greater than $20,000 but less than $40,000, the commission rate is 12%.
  • If sales are equal to or greater than $40,000, the commission rate is 14%

We apply the following steps:

  1. Open the worksheet that contains the sales dataset.
  2. TypAlt+F11to run itEdytor Visual Basic.
  3. ClickEnterin the menu bar and selectUnit of measureto insert a new unit.
Multiple if statements in Excel (nested IF, AND/OR) with examples (28)
  1. Enter the following VBA code.
Code developed by Steve Scott at https://spreadsheetplanet.comCOMMISSION function (Sales As Double) As Double Fixed Rate1 = 0.08 Fixed Rate2 = 0.105 Fixed Rate3 = 0.12 Fixed Rate4 = 0.14'Calculating Sales Commission Case99 For Sale COMMISSION = Sales * Rate1 Case 10000 to 19999.99: COMMISSION = Sales * Rate2 Case 20000 to 39999.99: COMMISSION = Sales * Rate3 Case >= 40000: COMMISSION = Select RateE4
  1. Save the function routine and workbook as oneWerkmap with macro support.
  2. Press Alt+F11 to switch to the active worksheet that contains the sales dataset.
  3. Select cell C2 and enter the following formula:
=COMMISSION(B2)
Multiple if statements in Excel (nested IF, AND/OR) with examples (29)
  1. Click Enter on the typing bar.
  2. Drag or double-click the fill handle to copy the formula to the column.

The commission for each seller is shown in column C.

Multiple if statements in Excel (nested IF, AND/OR) with examples (30)

This VBA function takes the sales amount as an argument and returns the corresponding commission.

A user-defined function is a much simpler and more readable solution than using nested IF functions.

This tutorial shows four examples of using nested IF statements in Excel and provides five alternatives to using multiple IF statements in Excel. We hope you found the tutorial helpful.

Other Excel lessons you might find useful:

  • How to logically test Excel using multiple if statements in Excel
  • How to use the IF function with dates in Excel (simple examples)
  • COUNTIF greater than zero in Excel
  • BETWEEN formula in Excel (with IF function) - Examples
  • Count cells smaller than value in Excel (COUNTIF less)

FAQs

Can you use if and and or together in Excel? ›

Use the IF function along with AND, OR and NOT to perform multiple evaluations if conditions are True or False. The condition you want to test. The value that you want returned if the result of logical_test is TRUE. The value that you want returned if the result of logical_test is FALSE.

Discover More Details ›
How do you use multiple IF or statements in Excel? ›

The multiple IF conditions in Excel are IF statements contained within another IF statement. They are used to test multiple conditions simultaneously and return distinct values. The additional IF statements can be included in the “value if true” and “value if false” arguments of a standard IF formula.

View More ›
Can you explain how nested IF statements are used in Excel and provide an example of their use? ›

Nested IF functions, meaning one IF function inside of another, allows you to test multiple criteria and increases the number of possible outcomes. We want to determine a student's grade based on their score. If Bob's score in B2 is greater than or equal to 90, return an A.

Learn More ›
Can you have 4 if statements in Excel? ›

Excel allows a max of 7 nested if statements. If we wanted to expand our list of possible statuses, we could add only one more condition and one more status. But fortunately we can add more using a different function.

Get More Info ›
Can you nest the AND OR OR functions within an IF function? ›

You can automate the evaluation of logical tests by NESTING the AND, OR, NOT functions inside a single IF function. This means that if we have multiple conditions but we want to return a single output, we can nest any of the conjunction functions inside an IF and specify outputs accordingly.

Know More ›
How do you use the advanced IF function in Excel? ›

For example, if C2>B2 is TRUE, return Over Budget. If the logical_test evaluates to TRUE and we don't provide the value for value_if_true, the function returns 0. Value_if_false is also optional. This is the value if the logical_test evaluates to FALSE.

Show Me More ›
How many nested IF statements in Excel? ›

As you see, it takes quite a lot of thought to build the logic of a nested IF statement correctly all the way to the end. And although Microsoft Excel allows nesting up to 64 IF functions in one formula, it is not something you'd really want to do in your worksheets.

See Details ›
What is nested if for multiple conditions? ›

The Excel nested IF function evaluates the logical tests in the order they appear in the formula, and as soon as one of the conditions evaluates to TRUE, the subsequent conditions are not tested. In other words, the formula stops after the first TRUE result.

Learn More Now ›
What is better multiple IF statements or one if with multiple conditions? ›

Answer. We can use multiple if statements when we want to check multiple conditions no matter if the previous if statement was true / false . With multiple if statements, each statement will be checked even when a previous statement evaluates to true .

Read On ›
How do you have multiple conditions in an if statement sheet? ›

How to Write an IF Statement with Multiple Conditions in Google Sheets?
  1. Type the IF function in the 'Action' column, followed by the AND function. ...
  2. Type the first condition as the first argument of the AND function. ...
  3. Add a comma and the second condition: the value in column C must be equal to “Yes”.
Feb 18, 2023

Keep Reading ›

What is an example of nesting of IF statements? ›

A nested if statement is an if-else statement with another if statement as the if body or the else body. Here's an example: if ( num > 0 ) // Outer if if ( num < 10 ) // Inner if System.

Read The Full Story ›
What is an example of nested if structure? ›

Example 1 : Check if three numbers are equal

If they are, then we go inside the nested if to check whether the third is equal to them. If yes, then all are equal else they are not equal. And if the first two numbers themselves are not equal to each other, no need to check further, we can simply output No.

Get More Info ›
What is an example of nested if then else? ›

IF weather = fine THEN DO SAY 'What a lovely day!' IF tenniscourt = free THEN SAY 'Shall we play tennis?' ELSE NOP END ELSE SAY 'Shall we take our raincoats?' Not matching nested IFs to ELSEs and DOs to ENDs can have some surprising results.

Read The Full Story ›
Can we enter multiple if condition in an if formula? ›

It is possible to nest multiple IF functions within one Excel formula. You can nest up to 7 IF functions to create a complex IF THEN ELSE statement. TIP: If you have Excel 2016, try the new IFS function instead of nesting multiple IF functions.

Keep Reading ›
How do you write an IF contains statement in Excel? ›

To check if a cell contains text, select the output cell, and use the following formula: =IF(ISTEXT(cell), value_to_return, ""). For our example, the cell we want to check is A2, and the return value will be Yes. In this scenario, you'd change the formula to =IF(ISTEXT(A2), "Yes", "").

Discover More Details ›

References

Top Articles
Latest Posts
Article information

Author: Edmund Hettinger DC

Last Updated: 01/12/2023

Views: 5875

Rating: 4.8 / 5 (78 voted)

Reviews: 85% of readers found this page helpful

Author information

Name: Edmund Hettinger DC

Birthday: 1994-08-17

Address: 2033 Gerhold Pine, Port Jocelyn, VA 12101-5654

Phone: +8524399971620

Job: Central Manufacturing Supervisor

Hobby: Jogging, Metalworking, Tai chi, Shopping, Puzzles, Rock climbing, Crocheting

Introduction: My name is Edmund Hettinger DC, I am a adventurous, colorful, gifted, determined, precious, open, colorful person who loves writing and wants to share my knowledge and understanding with you.