Convert long NESTED IF formulas to a simple formula with the CHOOSE function

Anyone who has used Excel for a while will have found on more than one occasion long formulas that use the IF function inside another IF that in turn is inside another IF. This is normally called NESTED IF.

An easy example of NESTED IF would be:

= IF (B1 = 1, "Apple", IF (B1 = 2, "Orange", IF (B1 = 3 "Pera", "Grape")))


Obviously instead of the name of the fruits we could write a formula, or a cell.

= IF (B1 = 1, A1, IF (B1 = 2, A1-C1, IF (B1 = 3, A1 * D1, A1 ^ 2)))


Well, this formula is pretty long (and it could be much longer since the maximum number of NESTED IF is 64!), but we could reduce that long formula to a simple formula using the CHOOSE function. This function allows us to choose a value from a list of values based on an index.

The syntax of this function is:


CHOOSE(index_num,value1,value2,...)

• Index_num (required):  specifies which value argument is selected. Index_num must be a number between 1 and 254, or a formula or reference to a cell containing a number between 1 and 254.
• value1 (required): First value to choose. Arguments can be numbers, cell references, defined names, formulas, functions, or text.
• value2 (optional): From the second value are optional and up to the value 254.

Using the CHOOSE function we could write formulas with NESTED IF like this:

= CHOOSE (B1, "Apple", "Orange", "Pear", "Grape")

where B1 represents the index from where to choose the fruit, so that if B1 = 1, the formula will return the first value which in this case is Apple.

= CHOOSE (B1, A1, A1-C1, A1 * D1, A1 ^ 2)

where according to the value that is in cell B1, a different formula is activated.

Well, you have now seen how the function CHOOSE can convert long and complicated formulas to understand to a simple formula.