Most people think that the IF function has to return a result. This leads to doing whole calculations in the true and false sections of the IF function. There is a way to create shorter functions.
In the image below we want to use the VLOOKUP function to look up the sales value in cell B2. The table to use in the VLOOKUP is determined by cell C2. If 1 is entered in cell C2 then the range F$2:$G$5 is used, otherwise the range $I$2:$J$5 is used.
Most people would enter the following formula to calculate this.
=IF(C2=1,VLOOKUP(B2,$F$2:$G$5,2),VLOOKUP(B2,$I$2:$J$5,2))
The IF function can return a range. This means that we can rewrite the above formula and shorten it. Instead of having two VLOOKUP functions, we can have one.
=VLOOKUP(B2,IF(C2=1,$F$2:$G$5,$I$2:$J$5),2)
In this case the IF function is returning the correct range of the table to use for the single VLOOKUP in the formula.
There is an alternative formula that is about the same length, but it involves the CHOOSE function.
The CHOOSE function works better than an IF function when there are multiple items that you are choosing between. In the above case we are only choosing between two table references. The alternative formula is
=VLOOKUP(B2,CHOOSE(C2,$F$2:$G$5,$I$2:$J$5),2)
The first argument in the CHOOSE function is a number. That number refers to the sequence number to extract from the subsequent entries listed in the CHOOSE function separated by commas. So a one will extract the first entry and a two will extract the second entry. You can see how this would simplify selecting between more than three entries when compared to an IF function.
Just like the IF function, the CHOOSE function can return ranges, values or text.
Thanks Neal fro a succinct helpful post. This technique is particularly handy for selecting a range based on a month number, and especially easy to follow when used in combination with sensible range names. By naming cell B2 as “Sales” and C2 as “Mth”, and naming the tables as “Rate_Jul” and “Rate_Aug” respectively, your formula becomes very easy to understand:
=VLOOKUP(Sales,CHOOSE(Mth,Rate_Jul,Rate_Aug),2)
Thanks James, yes the CHOOSE technique works well with range names – but then range names work well with most formula techniques.
You have also given me an idea that might become a new blog post – thanks.