Alternatives To Nested IF Functions

Category: Formulas | [Item URL]

Excel's IF function provides some simple decision-making capability to a worksheet. The IF function accepts three arguments:

• The condition being evaluated (should result in either TRUE or FALSE)
• The value to display if the condition is TRUE
• The value to display if the condition is FALSE

The formula below, for example, returns 1 if cell A1 contains "A". If cell A1 does not contain "A", the formula returns an empty string.

=IF(A1="A",1,"")

For more decision-making power, you can "nest" IF functions within a formula. In other words, you can use an IF function as the second argument for an IF function. Here's an example:

=IF(A1="A",1,IF(A1="B",2,IF(A1="C",3,"")))

This formula checks cell A1. If it contains "A", the formula returns 1. If it doesn't contain "A", then the second argument is evaluated. The second argument contains another IF function that determines if A1 contains a "B". If so, the formula returns 2; if not, the formula evaluates the IF function contained in the second argument and checks to see if A1 contains "C". If so, it returns 3; otherwise, it returns an empty string.

Excel allows up to seven levels of nested IF functions. The formula below works correctly, but Excel will not allow you to nest the IF functions any deeper than this.

=IF(A1="A",1,IF(A1="B",2,IF(A1="C",3,IF(A1="D",4,
 IF(A1="E",5,IF(A1="F",6,IF(A1="G",7,IF(A1="H",8,""))))))))

The sections that follow present various ways to get around the limit of seven nested IF functions. Be aware that these techniques may not be appropriate for all situations.

• Note:
  Excel 2007 and later allows up to 64 nesting levels

Using a VLOOKUP formula

In many cases, you can avoid using IF functions and use a VLOOKUP function. This will require a separate table in your worksheet. In the figure below, the lookup table is in B1:C10. The formula in A2 is:

=VLOOKUP(A1,B1:C10,2)

Using the CHOOSE function

In some cases, you can use the CHOOSE function. The first argument is an integer, and the value determines which of the subsequent arguments is evaluated and returned.

Using defined names

Another way to overcome the nested IF function limit is to use named formulas. Chip Pearson describes this technique at his web site, so I won't repeat it here.

• Nested Functions, at cpearson.com

Using the CONCATENATE function

Yet another option was suggested to me by B. Ganesh: Use the CONCATENATE function. In this case, each argument for CONCATENATE consists of an IF function. Here's an example:

=CONCATENATE(IF(A1="A",1,""),IF(A1="B",2,""),IF(A1="C",3,""),
IF(A1="D",4,""),IF(A1="E",5,""),IF(A1="F",6,""),IF(A1="G",7,""),
IF(A1="H",8,""),IF(A1="I",9,""),IF(A1="J",10,""))

The CONCATENATE function can handle as many as 30 arguments -- which equates to testing 30 different conditions.

And, as Alan Williams pointed out, you can avoid using the CONCATENATE function and use the concatenation operator (&):

   =IF(A1="A",1,"")&IF(A1="B",2,"")&IF(A1="C",3,"")
    &IF(A1="D",4,"")&IF(A1="E",5,"")&IF(A1="F",6,"")
   &IF(A1="G",7,"")&IF(A1="H",8,"")&IF(A1="I",9,"")
   &IF(A1="J",10,"")

This method is not limited to 30 comparisons.

Use Boolean multiplication

Another alternative, suggest by Daniel Filer is to use Boolean multiplication. This technique takes advantage of the fact that, when multiplying, TRUE is treated as 1 and FALSE is treated as 0. Here's an example:

=(A1="A")*1+(A1="B")*2+(A1="C")*3+(A1="D")*4+(A1="E")*5
+(A1="F")*6+(A1="G")*7+(A1="H")*8+(A1="I")*9+(A1="J")*10

Creating a custom VBA function

The final alternative is to create a custom worksheet function, using VBA. The advantage is that you can customize the function to meet your requirements, and your formulas can be simplified quite a bit.