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. |
CHOOSE SyntaxThe CHOOSE function has the following syntax:
|
CHOOSE TrapsThere are a couple of things to keep in mind when using CHOOSE:
|
Ex 1: Fiscal Quarter for Month NumberThe 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:
Representing months 1 to 12:
List Values in CHOOSE FunctionYou 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. 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 MondayThe 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 AddTo 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.
Formula for Next Monday's DateHere 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 StoreIn 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 WorksInstead 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,
|
Ex 4: Column Order for VLOOKUPFor 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 CHOOSEBelow 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 WorksThe 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:
|
Video: Create Random Text With CHOOSEIn 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 FileTo 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 |
Last updated: July 17, 2023 3:00 PM