30 Excel Functions in 30 Days: 05 – CHOOSE

Icon30DayYesterday, in the 30XL30D challenge, we got details on our operating environment, with the INFO function, and learned that it can no longer help with our memory issues. (Neither ours, nor Excel’s!)

For day 5 in the challenge, we’ll examine the CHOOSE function. This function, from the Lookup and Reference category, picks from a list of options, based on a number. In most cases though, it would be better to CHOOSE a different function, like INDEX and MATCH, or VLOOKUP. We’ll investigate those functions later in the challenge.

NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).

So, let’s take a look at the CHOOSE information and examples, and see what it can do, and what its weaknesses are. If you have other tips or examples, please share them in the comments.

Function 05: CHOOSE

The CHOOSE function returns a value from a list, based on an index number.

Choose00

How Could You Use CHOOSE?

The CHOOSE function can return a specific numbered item from a list, such as:

  • For month number, return the fiscal quarter number
  • Based on starting date, calculate the next Monday
  • For store number, show a sum of sales

CHOOSE Syntax

The CHOOSE function has the following syntax:

  • CHOOSE(index_num,value1,value2,…)
    • index_number must be between 1 and 254 (or 29 in Excel 2003 and earlier)
    • index_number can be typed in the function, or can be a formula or cell reference
    • index_number fractions will be truncated to the lowest integer, before using
    • value arguments can be numbers, cell references, defined names, formulas, functions, or text

CHOOSE Traps

In Excel 2003, and earlier versions, the CHOOSE function is limited to numbers between 1 and 29.  Lookup lists are usually easier to manage on a worksheet, instead of having them typed in a formula. With VLOOKUP or MATCH functions, you can refer to a worksheet list.

Example 1: Fiscal Quarter for Month Number

The CHOOSE function works well with a simple list of numbers as the values. For example, if cell B2 contains a month number, a CHOOSE formula can calculate the fiscal quarter for that month. In the example shown below, the fiscal year starts in July.

There are 12 values listed in the formula, representing the months from 1 to 12. The fiscal year starts in July, so months 7, 8 and 9 are in quarter 1.  In the table below, you can see the fiscal quarter below each month number.

Choose01a

For the CHOOSE function, you’ll enter the quarter value for each month, in the order that they appear in the table above. In the list of values for the CHOOSE function, items 7, 8 and 9  — for July, August and September, have a value of 1.

=CHOOSE(C2,3,3,3,4,4,4,1,1,1,2,2,2)

Enter a month number in cell C2, and the Fiscal Quarter is calculated by the CHOOSE function, in cell C3

Choose01b

Example 2: Calculate Date of Next Monday

The CHOOSE function can be combined with other functions, like WEEKDAY, to calculate upcoming dates. For example, if you’re in a club that meets every Monday night, you can find next Monday’s date, based on today’s date.

In the table below, you can see the weekday number for each day. In column H, you can see the number of days to each weekday, to get to the next Monday. On Sunday, you would need to add 1 day. If it’s Monday, there are 7 days till next Monday, and so on.

Choose02a

With the current date in cell C2, the formula in cell C3 uses the WEEKDAY and CHOOSE functions to calculate the next Monday.

=C2+CHOOSE(WEEKDAY(C2),1,7,6,5,4,3,2)

Choose02b

Example 3: Show Sales Total for Selected Store

You can use CHOOSE in formulas with other functions too, like SUM. In this example, we can get a total sales for a specific store, by entering its number in the CHOOSE function, and list the ranges to total for each store.

In this example, the store number (101, 102 or 103) is entered in cell C2. To get the index_number value, as 1, 2 or 3, instead of 101, 102, or 103, you can use a formula: C2-100.

The sales numbers for each store are in a separate column, as show below.

Choose03a

Inside the SUM function, the CHOOSE function will be evaluated first, and returns the correct range for the SUM, for the selected store.

=SUM(CHOOSE(C2-100,C7:C9,D7:D9,E7:E9))

Choose03b

This is example of a situation where other functions, like INDEX and MATCH, would be more efficient, and we’ll see how they work, later in the challenge.

Download the CHOOSE Function File

To see the formulas used in today’s examples, you can download the CHOOSE function sample workbook. The file is zipped, and is in Excel 2007 file format.

Watch the CHOOSE Video

To see a demonstration of the examples in the CHOOSE function sample workbook, you can watch this short Excel video tutorial.

YouTube link: Select Item in List With Excel CHOOSE Function

 

_____________

You may also like...

15 Responses

  1. Yard says:

    I think it’s worth stressing the usefulness of the application you describe in Example 3. CHOOSE can be used as an effective alternative to INDIRECT, the advantage being most apparent when multiple instances of the volatile INDIRECT are required.

    I have used it many times when compiling a large data table, when I want one column to look up a value in one of several tables. The choice of the table in which to lookup the value could be made with an IF statement (which can get messy if there are multiple nestings), an INDIRECT function (which is calculation-intensive) or a CHOOSE function (which has a simple syntax).

    Now don’t tell me that CHOOSE is also volatile… :-)

  2. squiggler says:

    I often use CHOOSE as an alternative to IF, it works well with CHOOSE(2+SIGN(A1),-1,0,1), given that it indexes the values rather than perform a comparison, it can also out-perform MATCH/(V/H)Lookup’s!

    And since the other values are not evaluated unless chosen, offers several advantages over other methods!

  3. Eric says:

    Does anyone understand the reasoning for the limitations on CHOOSE (i.e., 29 or 254, depending on version)?

    I was trying to find a work around and couldn’t, of course.

    But you can use =INDEX(BigListOfValuesInNamedOneColumnRange, YourChoice, 1) to emulate CHOOSE on more than the limitations.

  4. Reuvain says:

    CHOOSE can also be used to piece together an array of non-contiguous ranges. This can be very useful if you want to do a VLOOKUP to the left. (I.e. looking up the value in a column on the right side of a table and retrieving the value from a column on the left side of the table.) For example if your table is the range A1:C10 and you want to lookup a value in column C and return the corresponding value in column A you could use the following formula:
    =VLOOKUP(lookup_value,CHOOSE({1,2},C1:C10,A1:A10),0)

  5. Reuvain says:

    I left out the column_index_num in the VLOOKUP formula.
    The formula should have been:
    =VLOOKUP(lookup_value,CHOOSE({1,2},C1:C10,A1:A10),2,0)

  6. Reuvain says:

    It looks like my first post didn’t make it through.
    Here it is.
    CHOOSE can also be used to piece together an array of non-contiguous ranges. This can be very useful if you want to do a VLOOKUP to the left. (I.e. looking up the value in a column on the right side of a table and retrieving the value from a column on the left side of the table.) For example if your table is the range A1:C10 and you want to lookup a value in column C and return the corresponding value in column A you could use the following formula:
    =VLOOKUP(lookup_value,CHOOSE({1,2},C1:C10,A1:A10),2,0)

    http://www.excelforum.com/2418540-post8.html

  7. Kirill Lapin (KL) says:

    2Yard:
    CHOOSE() isn’t volatile. There are very few volatile functions and they are listed here: Volatile Excel Functions

    INDEX has always been the non-volatile alternative for INDIRECT(). The major difference between CHOOSE and the other index-based alternatives (INDEX+MATCH, VLOOKUP, HLOOKUP, LOOKUP) is that it allows you to choose not only between values or references, but also between independent formula expressions and to use references on different sheets at the same time. E.g.: =CHOOSE(2,A1+3,VLOOKUP(A1,Table,3,0),Sheet2!A1).
    2Eric:
    There is another, “more native”, but less known option with INDEX, which btw isn’t subject to the max number of arguments limitation (because of the use of the union syntax):
    =INDEX((A1:A10,B3:B13,C5:F25,…,X10:Z90), , ,YourChoice)
    As a side-note:
    One of the most special things about CHOOSE is that it is one of the very few functions (INDEX,OFFSET,INDIRECT,CHOOSE and IF) that can return actual reference, so the following syntax is possible:
    =SUM(CHOOSE(2,A1,B1):D10)

  8. Hugo says:

    @Reuvain
    I appriciate your hint:
    =VLOOKUP(lookup_value,CHOOSE({1,2},C1:C10,A1:A10),0)
    Works fine for me.
    But I don’t understand the part {1,2} within the CHOOSE formula.
    Can you (or any other expert) explain this in short?
    Thank you.

  9. Eric says:

    The {1,2} creates a union of A1:A10 and C1:C10, with the first column being the values from C1:C10 and the second column A1:A10. This would be the database part of the VLOOKUP formula. That database would be 2 columns and 10 rows… very cool trick!

  10. Reuvain says:

    That’s right Eric. I am entering the array {1,2} for the index_number argument. This allows me to CHOOSE more than one value. In this case my values are columns, so by choosing more than one of them it builds me a two-dimensional array which I am using for the table_array argument in the VLOOKUP formula. It’s great that the VLOOKUP formula accepts an array in addition to an range reference.

  11. Hugo says:

    Thank you.
    I found the point at the end of Excel’s help for CHOOSE:
    “If index is a matrix, then every value will be used when evaluating CHOOSE.”

    That means in {1,2} CHOOSE takes at first index 1 (that is C1:C10) and then index 2 (A1:A10).
    Now understand fully.

  12. Ulrik says:

    Boy, I am glad I took the time to read through the comments:-)
    Using CHOOSE to do VLOOKUPS in a noncontigous range left or right is really a nifty trick!

  13. Contextures Blog » 30 Excel Functions in 30 Days: 21 - TYPE says:

    […] could use the TYPE function with CHOOSE, to multiply valid numbers, or show a message, if something else is […]

  14. Contextures Blog » Excel Price Lookup: VLOOKUP or INDEX says:

    […] the CHOOSE function returns a reference to the selected […]

  1. August 21, 2014

    […] time, I tested a new method – using RANDBETWEEN inside a CHOOSE formula. Both lists of options were short – 2 genders and 3 regions – so they would work nicely as […]

Leave a Reply

Your email address will not be published. Required fields are marked *