The Choose function in Excel is little used but extremly versatile formula. It works very similar to a lookup function. The following is the argument for the Choose function.
CHOOSE(index_num,value1,value2, value3.etc...)
It can be used simply to return data which is found in a list of values. Here is a simple example;
You have quartely data. Jan Feb Mar, use choose in the following way.
=CHOOSE(2,"Jan","Feb","Mar")
The 2 at the start is the index number which in this case is February, the second item in the list.
Suppose we have names vertically and qantaties horizontally. Now in a data validation box you have quantities.
The choice chosen in the data validation list will determin which column to sum by. So if we choose Qty1 from the dropdown we want to see the column titled Qty 1 summed.
The same can be true if we choose Qty 2, this column becomes the focus of the sum formula.
The formula is as follows.
=SUM(CHOOSE(MATCH(H10,C10:E10),C11:C20,D11:D20,E11:E20))
There are other ways to do the above such as using the old faithful sumproduct formula.
=SUMPRODUCT((C10:F10=H10)*(C11:F20))
The choose function can be used to determine quarters. The following choose formula:
=CHOOSE(MONTH(C1),1,1,1,2,2,2,3,3,3,4,4,4)
However, this formula can be replaced by the following lookup formula:
=LOOKUP(MONTH(A1), {1,4,7,10},{1,2,3,4})
which is a little more succinct. The choose formula is valuable however, the use of similar functions is worth exploring, Vlookup, Lookup, Index and match all perform very similar actions. These functions are all outlined in some way inside thesmallman.com Native Excel section.
The methods discussed are outlined in the attached Excel file.
