CHOOSE function

The tutorial explains the syntax and basic uses of the CHOOSE function and provides a few non-trivial examples showing how to use a CHOOSE formula in Excel.

CHOOSE is one of those Excel functions that may not look useful on their own, but combined with other functions give a number of awesome benefits. At the most basic level, you use the CHOOSE function to get a value from a list by specifying the position of that value. Further on in this tutorial, you will find several advanced uses that are certainly worth exploring.

Excel CHOOSE function - syntax and basic uses

The CHOOSE function in Excel is designed to return a value from the list based on a specified position.

The function is available in Excel 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel 2007.

The syntax of the CHOOSE function is as follows:

CHOOSE(index_num, value1, [value2], …)

Where:

1. Index_num (required) - the position of the value to return. It can be any number between 1 and 254, a cell reference, or another formula.
2. Value1, value2, … - a list of up to 254 values from which to choose. Value1 is required, other values are optional. These can be numbers, text values, cell references, formulas, or defined names.

Here's an example of a CHOOSE formula in the simplest form:

=CHOOSE(3, "Mike", "Sally", "Amy", "Neal")

The formula returns "Amy" because index_num is 3 and "Amy" is the 3rd value in the list:

Excel CHOOSE function - 3 things to remember!

CHOOSE is a very plain function and you will hardly run into any difficulties implementing it in your worksheets. If the result returned by your CHOOSE formula is unexpected or not the result you were looking for, it may be because of the following reasons:

1. The number of values to choose from is limited to 254.
2. If index_num is less than 1 or greater than the number of values in the list, the #VALUE! error is returned.
3. If the index_num argument is a fraction, it is truncated to the lowest integer

How to use CHOOSE function in Excel - formula examples

The following examples show how CHOOSE can extend the capabilities of other Excel functions and provide alternative solutions to some common tasks, even to those that are considered unfeasible by many.

Excel CHOOSE instead of nested IFs

One of the most frequent tasks in Excel is to return different values based on a specified condition. In most cases, this can be done by using a classic nested IF statement. But the CHOOSE function can be a quick and easy-to-understand alternative.

CHOOSE formula to return a custom day/month name from date

In situations when you want to get a day name in the standard format such as full name (Monday, Tuesday, etc.) or short name (Mon, Tue, etc.), you can use the TEXT function as explained in this example: Get day of week from date in Excel.

If you wish to return a day of the week or a month name in a custom format, use the CHOOSE function in the following way.

To get a day of the week:

=CHOOSE(WEEKDAY(A2),"Su","Mo","Tu","We","Th","Fr","Sa")

To get a month:

=CHOOSE(MONTH(A2), "Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

Where A2 is the cell containing the original date.

I hope this tutorial has given you some ideas of how you can use the CHOOSE function in Excel to enhance your data models. I thank you for reading and hope to see you on our blog next week!