The IF function is typically used to display one result or text when a condition is met and another if it is not met. There is another technique using the IF function that can reduce formula size in certain circumstances.

For example let’s take the situation where you want to SUM column B if cell A1 is negative but SUM column C if A1 is zero or positive. The normal formula would look like this

=IF(A1<0,SUM(B:B),SUM(C:C))

The thing to note in this IF function is that both the TRUE and the FALSE actions involve the SUM function. The only difference is that you’re adding up different columns. You could rewrite the above formula as follows

=SUM(IF(A1<0,B:B,C:C))

As you can see this is a shorter formula and doesn’t repeat the SUM function. The IF function is returning the range (a whole column in this case) to use in the SUM function.

In the above example it doesn’t make a huge difference to the formula because the SUM function is straightforward. It makes a bigger difference when you are using longer functions that have more components e.g. VLOOKUP.

Compare the two formulas below that perform exactly the same calculation.

=IF(A1<0,VLOOKUP(H1,$B$1:$C$2,2,0),VLOOKUP(H1,$E$1:$F$2,2,0))

=VLOOKUP(H1,IF(A1<0,$B$1:$C$2,$E$1:$F$2),2,0)

The only difference between the TRUE and FALSE actions is the table used in the VLOOKUP. The VLOOKUP is repeated in the first IF function but not in the second.

In the second formula the IF function is returning the table to use in the VLOOKUP function.

If the TRUE and FALSE parts of your IF function are performing similar calculations with different ranges then this technique can work for you.