As part of my mission to encourage modellers to reduce reliance on the IF function, this month’s blog looks at situations when either CHOOSE or INDEX with MATCH can replace IF.
The IF function is usually one of the first functions financial modellers learn and habits are always hard to change. However, IF is rarely the best choice of function. It is often used when:
- An on / off switch is required. Use logical statements instead (see September’s blog).
- A minimum or maximum is required. Use MIN or MAX instead (see October’s blog).
- An option or scenario is selected. Sometimes modellers use a nested IF function for this which is not the most beautiful type of formula. This blog looks at some alternatives.
Take an example where there are three different percentage options: C2, C3 and C4 depending on which scenario is in use. The scenario in use is selected using cell B6:
| A | B | C | |
| 1 | Scenario: | Percentage: | |
| 2 | 1 | 100% | |
| 3 | 2 | 90% | |
| 4 | 3 | 80% | |
| 5 | |||
| 6 | In use: | 1 | 100% |
The purpose of cell C6 is to pick up the percentage, depending on which scenario is selected in cell B6. Here are some of the functions which could be used to achieve this:
IF Function:
This is the clumsy “nested” IF function:
IF( B6 = 1, C2, IF( B6 = 2, C3, IF( B6 = 3, C4, 0 )))
Translated as:
If B6 = 1, then C2, otherwise
If B6 = 2, then C3, otherwise
If B6 = 3, then C4, otherwise 0
This formula has several problems:
- It is long and difficult to follow.
- There is a high error rate with nested IF functions.
- The use of the numbers 1,2,3 is bad modelling practice (no numbers in formulae except for 1 or 0).
These are the alternatives which I recommend instead:
Alternative 1: CHOOSE
For up to four different options I would recommend a CHOOSE function:
CHOOSE ( B6 , C2 , C3 , C4)
For this function B6 must equal a number, in this case either 1, 2 or 3.
The formula translates as follows:
When B6 = 1, the result is C2
When B6 = 2, the result is C3
When B6 = 3, the result is C4
CHOOSE is a good choice when:
- There are a limited number of options. This is because the options need to be listed individually rather than as a range.
- The options are not necessarily on adjacent lines.
One of the advantages of a CHOOSE function is that, on a QWERTY keyboard, the shortcut Ctrl [ will instantly bring you back to the first part of the formula (in this case B6).
Alternative 2: INDEX with MATCH
However, with more options to select I recommend an INDEX with a MATCH function:
INDEX ( C2 : C4 , MATCH ( B6 , B2 : B4 , 0) )
The MATCH function returns the position of B6 in the range B2 : B4. The zero in the function looks for an exact match and should always be included. Therefore, when B6 = B2, the result of the MATCH function is 1 which means that:
When B6 = B2, the result of the INDEX function is C2 (the first cell in the range C2:C4)
When B6 = B3, the result is C3 (the second cell in the range C2:C4)
When B6 = B4, the result is C4 (the third cell in the range C2:C4)
BUT BE CAREFUL WITH INDEX! I always use it with a MATCH function. This is because if the formula INDEX ( C2 : C4 , B6) is used and blank rows are inserted inside the range C2 : C4, the positions of the cells move and therefore the results of the INDEX function would be incorrect.
INDEX with MATCH is a good choice when:
- There are more than four options as a range is selected (rather than individual cells).
- The options are on adjacent lines.
- The cell that needs to be matched (B6) is not a whole number as the MATCH function will match anything (whereas CHOOSE can only select from whole numbers between 1 and 254).
Therefore, next time you consider using an IF function to select an option, try and use one of these alternatives.
And trust me. My early models were built almost entirely with IF functions as it was almost the only function I knew. But I have been converted over the years and now I avoid IF whenever I can.