We often come across situation where we have to select from a list of available options in excel. Let’s take an example different 5 regions for sales and for each region you have assigned numerical values from 1-5. Now you wish to just put any number 1-5 to get the sales for that region. And in this case your CHOOSE() function comes handy.
Lets first know how a choose function works:
The syntax of CHOOSE() function is CHOOSE(index_value, value1, value2….value5)
Here index_value is the position of the number in the list of values to return. In this case it is 1-5. A value can be any one of the following: a number, a cell reference, a defined name, a formula/function, or a text value. Therefore if you use a CHOOSE() function like:
CHOOSE(3,”Tea”,”Coffee”,”Hot Chocolate”). Then this would return Hot Chocolate.
Now back to our situation described above.
Step:1 Assign number from 1-5 to each region like
1 Asia Pacific
2 UK
3 US
4 Africa
5 Middle East
Step 2: You can now input any number from 1-5 in Cell D3 and use the 2 CHOOSE functions in any other cells as shown here CHOOSE($D$2,B3,B4,B5,B6,B7) and CHOOSE($D$2,C3,C4,C5,C6,C7) to get the desired result. Use the following attached sample file for your understanding.
