Understanding Excel CHOOSE() function [Intro]

Understanding Excel CHOOSE() function [Intro]

In essence whatever we give to life it returns the same. If we give peanuts, it will return peanuts. If it is 1 life will return 1, if it is 2 life will return 2. So life is a perfect response to our intentions and actions. Whatever we input, life processes that input and returns the favour.

Diving deeper into this philosophy, it seems like if our destiny already has different packages ready for us. If give poor then life selects the package that has poor returns, if we do better then life selects the package with better returns.

This is exactly what Excel’s CHOOSE() function does. It chooses the package (or value) based on the number (input) specified. For example if we mention 1 it will select value 1, if we mention 2 it will select the value 2, if we mention 3 it will select value 3 and so on. Lets understand the function in detail. I like details.

Lets learn to be CHOOSY!

CHOOSE function has following syntax:

CHOOSE(index_num, value1, [value2], [value3], …)

where

index_num: is the number we have to provide. It can be a hard-coded figure or a cell address that has a number or another function that helps get a number. So it all boils down to a numbah! And based on this number function will return the value which we mention in the next argument.

• It can be any number between 1 and 254 (including 1 and 254). It cannot be less than 1 or greater than the number of options/values mentioned otherwise it will return a #VALUE error.
• It cannot be 0. If 0 is given as an argument then it will return #VALUE error.
• If it is a fraction or not a whole number then it is reduced to the lowest integer but it must be not less than 1.

value 1, [Value 2], [Value 3]: are the packages or options that we provide to Excel. It has to be at least one option for Excel to choose from that is why Value 1 is mandatory but later ones are optional. This formula can hold up to 254 options/packages/values.

• It can either be a text, number or a range of cells.
• It can be a named range (you might have to wrap the CHOOSE function in another function to get it to work. Thanks to Erdol. Check the comment section for an example.)
• It can have a formula or a function.

OK Enough of theory. As you can see the application and use of the function is pretty straight forward. But don’t underestimate the power of Excel’s function as it depends on how you choose to use it.

Example 1: Hard-coded information

In cell B2 put this formula:

=CHOOSE(2,100,200,300,400,500,600,700,800,900,1000)

Hit enter key and it will result in 200. Lets understand it why it resulted in 200.

Following is the formula with the syntax of the formula just below:

=CHOOSE(	2,	100,	200,	300,	400,	500,	600,	700	)
=CHOOSE(	Index_num,	Value 1,	[Value 2],	[Value 3],	[Value 4],	[Value 5],	[Value 6],	[Value 7]	)

As you can see that for index_num argument we have 2 i.e. we are asking Excel to choose second value from the options we mentioned. So it will go to second value and will return whatever is mentioned as Value 2 there which in our case is 200. So 200 will be the resultant

Here is another example:

=CHOOSE(	4,	“A”,	“B”,	“C”,	“D”,	“E”,	“F”,	“G”	)
=CHOOSE(	Index_num,	Value 1,	[Value 2],	[Value 3],	[Value 4],	[Value 5],	[Value 6],	[Value 7]	)

The result of this will be D as in value 4 the provided value is D.

Example 2: Using cell reference as arguments

As stated earlier, you don’t have to give hard values to function to work. Both index_num and Value arguments can have cell reference(s) to get the job done.

Using cell reference as index_num argument

Following is the example where cell reference is used as index_num:

=CHOOSE(	A1	“January”,	“February”,	“March”,	“April”,	“May”,	“June”,	“July”	)
=CHOOSE(	Index_num,	Value 1,	[Value 2],	[Value 3],	[Value 4],	[Value 5],	[Value 6],	[Value 7]	)

Now cell A1 have to contain a number which can be any number from 1 to 7. Lets say it is A1 contain 7 then the result will be July.

Using cell reference as value argument

Just like indux_num can have cell reference, similarly values argument can house cell references as the following example shows:

=CHOOSE(3,B1,C1,D1,E1,F1,G1,H1,I1)

Following animation shows how this formula is working:

And finally if both index_num and value argument have cell references then it is even more easy to make it dance 🙂 Have a look at the following illustration and you can see that by changing the value in cell B2 the formula updates automatically to fetch the correct value:

Example 3: Using another function/formula as arguments

Just like many other Excel functions, CHOOSE function can also hold other functions as arguments in place of index_num and Value. Following examples illustrate the process.

Using function as Value argument

Lets say you have a sales data and you may desire to sum to get the total or find the average sales or get the minimum or maximum sales in the period. This can be done easily using CHOOSE function and requires only one time effort.

Suppose you have figures from East, West, North and South of your operations in B2:B5. In cell D7 put this formula and hit Enter key:

=CHOOSE(1,SUM(B2:B5),AVERAGE(B2:B5),MAX(B2:B5),MIN(B2:B5))

This will sum all the sales figures as the index_num given is 1 and in value 1 we have SUM(B2:B5).

Similarly:

1. To get average sales just change index_num to 2
2. To get highest sales figure use 3 as index_num
3. To get the lowest sales use 4

Following illustration shows it in action:

Using function as Index_num argument

In the example above where we learnt that we can use other functions as value argument we still hard punched the index_num but it can also hold a function.

Lets say we want to select the operation to be performed from the drop down menu and based on the selection CHOOSE function should perform the relevant task as it did in previous example i.e. SUM, AVERAGE, MAX and MIN. To get this done we need operations to be in the list with the relevant index_num numbers in another column and then using VLOOKUP we can get it to work.

Following are the steps to pull this feat:

Step 1: To get the drop-down menu go to Data tab and click Data validation button in the data validation tools group. In the dialogue box from allow drop down select list and in the address mention the range that contains the operations. In our case the drop down is in cell A8

Step 2: Go to cell where you want the result and put this formula. In our case it will be cell B8:

=CHOOSE(VLOOKUP(A8,D2:E5,2,FALSE),SUM(B2:B5),AVERAGE(B2:B5),MAX(B2:B5),MIN(B2:B5))

This function has following parts:

Index_num: VLOOKUP(A8,D2:E5,2,FALSE)
Remember, A8 is the cell that has drop down menu from where we can select the operation to be performed.

Value 1: SUM(B2:B5)

Value 2: AVERAGE(B2:B5)

Value 3: MAX(B2:B5)

Value 4: MIN(B2:B5)

So what happen here is that when this function is executed it needs index_num and to get that it needs to initiate VLOOKUP which depends on the value mentioned in cell A8.

If we have selected the Sum from the menu then VLOOKUP will go to the range D2:E5 that has operations and relevant numbers listed. It will search for Sum and then look for corresponding number which is “1” and this index_num will be 1.

Having 1 as index_num CHOOSE function will fetch the value 1 which is SUM(B2:B5) and thus the sum of sales figure will be the result.

Following animation shows the whole process: