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.

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

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

- 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

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 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

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 VLOOKUP or MATCH functions, you can refer to a worksheet list.

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 would look like this:

**3,3,3, 4,4,4, 1,1,1, 2,2,2**

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.

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.

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 Friday add 3 days to get 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)**

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, 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.

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**

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.

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.

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:

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

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

Last updated: June 10, 2021 7:57 PM

Contextures RSS Feed