Home > Formulas > Lookup > CHOOSE

CHOOSE Function Examples

Excel CHOOSE function examples show how to select from a list of options, based on a number. A key benefit of CHOOSE is that it can return a value or reference. However, in many cases, a different function, such as INDEX and MATCH, or VLOOKUP, would be more efficient.

Video: CHOOSE Function

The CHOOSE function returns a value or a reference from a list of options, based on an index number.

To see how the Microsoft Excel CHOOSE function works, watch this short video tutorial. It shows some of the examples from this tutorial.

Uses for CHOOSE

• For month number, return the fiscal quarter number
• Based on starting date, calculate the next Monday
• For store number, show a sum of sales
• Combine columns in different order, for VLOOKUP

CHOOSE Syntax

The CHOOSE function has the following syntax:

• CHOOSE(index_num,value1,value2,...)
  • index_num must be between 1 and 254 (limit of 29 in Excel 2003 )
  • index_num can be typed in the function, or can be a formula or a valid cell reference
  • index_num 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

There are a couple of things to keep in mind when using CHOOSE:

• If the the index_num is less than 1, or higher than the number of items in the list, the result is a #VALUE! error
• 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 XLOOKUP, VLOOKUP or MATCH functions, you can refer to a worksheet list.

Ex 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 - month 7.

If January to December are listed with their fiscal quarter numbers, the list of fiscal quarters would look like this:

• 3,3,3, 4,4,4, 1,1,1, 2,2,2

Representing months 1 to 12:

• Jan,Feb,Mar,  Apr,May,Jun,  Jul,Aug,Sep,  Oct,Nov,Dec

List Values in CHOOSE Function

You can use that list in a CHOOSE formula, to return the fiscal quarter number for a specific month.

• Enter a month number in cell C2
• Put this formula in cell C3:
  • =CHOOSE(C2, 3,3,3,4,4,4,1,1,1,2,2,2)

If you enter 5 as the month number, the result is 4, so May is in the 4th fiscal quarter.

Or, if you enter 1 as the month number, the result is 3 -- January is in the third fiscal quarter, and the first value listed in the formula is 3.

Ex 2: Date of Next Monday

The CHOOSE function can be combined with other functions, like WEEKDAY, to calculate upcoming dates. For example, if you have a staff meeting every Monday, use a formula to find next Monday's date.

      Today's Date + ?? days = Next Monday's Date

The formula will add a specific number of days to today's date, and the CHOOSE function will pick that number.

How Many Days to Add

To build the list of items for CHOOSE, we need to know today's WEEKDAY number.

• The days are numbered from Sunday (1) to Saturday (7), if you use the default settings for WEEKDAY.
• If today is Sunday the WEEKDAY number is 1.
• If today is Friday the WEEKDAY number is 6.

This table shows the WEEKDAY numbers, with day names, and the number of days until the next Monday.

The numbers in the Add column will be used in the CHOOSE function - 1,7,6,5,4,3,2.

• If today is Sunday add 1 day to get next Monday's date.
• If today is Thursday, add 4 days to get next Monday's date
• If today is Friday add 3 days to get next Monday's date.

Formula for Next Monday's Date

Here is the formula to calculate next Monday's date, if today's date is in cell C2:

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

Ex 3: Sales Total for Selected Store

In this example, select a store from a combo box drop down, and a formula will calculate its total sales, over 3 months.

The worksheet has a Form Control combo box that shows a list of three stores. The combo box is linked to cell C2 (orange colour), which shows the item number that you selected -- 1, 2 or 3.

In cell C3, a formula combines SUM and CHOOSE, to show the 3-month total for the selected store.

=SUM(CHOOSE(C2,C6:C8,D6:D8,E6:E8))

When Store103 is selected, it shows the total for cells E6:E8 - 1774.

How It Works

Instead of a list of items, this CHOOSE function has a list of range references - C6:C8, D6:D8, E6:E8. Those ranges have the monthly sales for each of the three stores.

When Store103 is selected,

• CHOOSE is evaluated first, and it returns a reference to the 3rd range: E6:E8
• SUM returns the total for that range: 1774

Ex 4: Column Order for VLOOKUP

For the VLOOKUP function, the lookup value must be in the first column at the left of the lookup range. In this example, the CHOOSE function will combine columns in a different order, so a VLOOKUP formula can return a result from a column to the left of the lookup column.

Here is the lookup table, with the lookup values -- Scores -- in column D. We want to get the description from column C, using an approximate match for a Score.

The CHOOSE function will allow us to do that, without changing the table layout.

VLOOKUP and CHOOSE

Below the lookup table, a score is entered in cell C9.

To find the matching grade, the following formula is entered in cell C10, combining VLOOKUP with CHOOSE.

The index_num argument for CHOOSE has two numbers, typed inside curly brackets - {1,2}

=VLOOKUP(C9,CHOOSE({1,2},D3:D7,C3:C7),2,TRUE)

When 65 is entered as the score, the result is Fair.

How It Works

The CHOOSE function creates a 2-column Lookup Array for the VLOOKUP function. To do that, this formula uses an array for the first argument -- {1,2} -- instead of a single number.

The array puts Scores (D3:D7) in the first column, and Description (C3:C7) in the second column.

• CHOOSE({1,2}, D3:D7, C3:C7)

Then, VLOOKUP returns the value from the 2nd column in that array (C3:C7), using an approximate match.

To see the array that CHOOSE creates:

1. Select the CHOOSE function in the formula bar
2. Press the F9 key to evaluate that part of the formula. Each pair of scores/descriptions is evaluated.
3. Press the Esc key, to exit the formula, without saving the changes

Video: Create Random Text With CHOOSE

In this video, see how CHOOSE is combined with the RANDBETWEEN function to create random text results in Excel. RANDBETWEEN returns random numbers, and when combined with CHOOSE, it can even create random text.

See the steps in this video, and there are written instructions and a sample file on the Excel RAND and RANDBETWEEN Functions page.

Download the Sample File

To see the formulas used in these examples, download the CHOOSE function sample workbook. The file is zipped, and is in Excel xlsx file format. There are no macros in the workbook

More Tutorials

INDIRECT Function

VLOOKUP Function

INDEX and MATCH Functions

CELL

TEXT