Nested IF with multiple AND

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))))

Explanation

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.