Home > Formulas > Lookup > CHOOSE CHOOSE Function ExamplesExcel 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.
The CHOOSE function has the following syntax:
There are a couple of things to keep in mind when using CHOOSE:
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.
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.
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.
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,
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.
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:
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: January 7, 2023 11:57 AM