Excel: Nested IF Functions

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.

The syntax for the nesting the IF function is:

IF( condition1, value_if_true1, IF( condition2, value_if_true2, value_if_false2 ))

This would be equivalent to the following IF THEN ELSE statement:

IF condition1 THEN
    value_if_true1
ELSEIF condition2 THEN
    value_if_true2
ELSE
    value_if_false2
END IF

This syntax example demonstrates how to nest two IF functions. You can nest up to 7 IF functions.

condition is the value that you want to test.

value_if_true is the value that is returned if condition evaluates to TRUE.

value_if_false is the value that is return if condition evaluates to FALSE.

Applies To:

Excel 2007, Excel 2003, Excel XP, Excel 2000

For example:

Let's take a look at an example:

Based on the Excel spreadsheet above:

=IF(A1="10X12",120,IF(A1="8x8",64,IF(A1="6x6",36))) would return 120

=IF(A2="10X12",120,IF(A2="8x8",64,IF(A2="6x6",36))) would return 64

=IF(A3="10X12",120,IF(A3="8x8",64,IF(A3="6x6",36))) would return 36

Frequently Asked Questions

Question: In Excel, I need to write a formula that works this way:

If (cell A1) is less than 20, then times it by 1,
If it is greater than or equal to 20 but less than 50, then times it by 2
If its is greater than or equal to 50 and less than 100, then times it by 3
And if it is great or equal to than 100, then times it by 4

Answer: You can write a nested IF statement to handle this. For example:

=IF(A1<20, A1*1, IF(A1<50, A1*2, IF(A1<100, A1*3, A1*4)))

Question: In Excel, I need a formula in cell C5 that does the following:

IF A1+B1 <= 4, return $20
IF A1+B1 > 4 but <= 9, return $35
IF A1+B1 > 9 but <= 14, return $50
IF A1+B1 > 15, return $75

Answer: In cell C5, you can write a nested IF statement that uses the AND function as follows:

=IF((A1+B1)<=4,20,IF(AND((A1+B1)>4,(A1+B1)<=9),35,IF(AND((A1+B1)>9,(A1+B1)<=14),50,75)))