Exceljet

Quick, clean, and to the point

Nested IF with multiple AND

Excel formula: Nested IF with multiple AND
Generic formula 
=
IF(AND(A1="x",B1>=100),1.5,
IF(AND(A1="y",B1< 100),1.4,
IF(AND(A1="x",B1>=100),1.3,
IF(AND(A1="y",B1< 100),1.2,
1.1))))
Explanation 

To evaluate several options with a nested IF statement, one approach is to use a separate IF per line to show the result of each set of logical tests. By adding line breaks after each IF function, the formula becomes self-documenting. In the example shown, the formula in D5 is:

=
IF(AND(B5="red",C5>=100),1.5,
IF(AND(B5="red",C5<100),1.4,
IF(AND(B5="blue",C5>=100),1.3,
IF(AND(B5="blue",C5<100),1.2,
1.1))))

How this formula works

This formula relies on a technique called "nested IFs" to handle a series of options and results. With nested IFs, one IF function is nested inside another, a process that is explained in some detail here.

The formula in this example is purposely more verbose that necessary in order to "show" all possible options and results in a way that is easier to understand and maintain. The trick is to structure the formula with line breaks to show each IF on a separate line along with the "true result" for that IF. The "false result" is the following IF statement. Notice the final false result will "catch" any case that fails all previous tests.

Essentially, we are using line breaks to build a "table"that the human eye can easily read. To limit IF functions, we are using the AND function to run more than one logical test at a time. The AND function inside each IF function tests both color and value.

Note can use use Alt + Enter to enter to enter new lines in the formula bar. You'll need to expand the formula bar vertically in order to see more than one line at a time.

More conditions

This formula approach can be expanded to evaluate more options. The AND function can handle more logical tests, and you can combine the AND function with the OR function if needed. You could also replace AND and OR with boolean logic. Finally, you can also use the IFS function in later versions of Excel to reduce nesting.

Result as calculation

Although the example above shows a numeric result for each set of options, the formula can be customized to run a calculation instead by replacing hardcoded values with any standard formula expression.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course
Video training for Excel Tables