In this simple guide, we will run through how to use the Excel CHOOSE function, with examples. We will cover:
- What is the Excel CHOOSE function?
- The formula
What is the CHOOSE function in Excel?
CHOOSE is a relatively simple function on the surface that let’s users enter a list of values, characters, phrases and so on, and allow the user to literally choose one of them based on the input criteria.
The way this can be done really depends on the set up of the formula. The value can be hard coded into the formula itself as a number, but can also reference a specific cell which gives the user of the document a bit more power with this function.
By layering the CHOOSE formula in Excel with other formulas such as RANDBETWEEN we can add a whole new layer to the approach by essentially turning Excel into a random generator.
Before we run through some examples, let’s take a quick look at the syntax required for the Excel CHOOSE formula itself.
The CHOOSE formula
This one is relatively simple to work with and follows the below syntax:
=CHOOSE(index_num, Value1, Value2, Value3)
Note that the number of values you enter is entirely based on how many you want to run with. If you only want four options, just enter four values. The maximum number of values to enter in a list is 254.
Let’s look at a quick example. If we have all four seasons in a list (Summer, Fall, Winter, Spring), and enter them in that order, we simply choose 2 for Fall, 4 for Spring and so on:
As mentioned above, we don’t necessarily need to enter the number itself, but can reference a cell too using a lookup table.
On top of this, we don’t actually need to enter the literal terms either in the formula, but can reference a series of cells. This makes life a lot easier when we have multiple formulas referencing the same list and we need to make quick changes. In this case we just need to edit a single cell rather than a string of characters in each and every formula that uses it.
Below we have a range of formulas all taking different approaches – literal numbers, reference cells, literal strings, and string reference cells:
Going one step further, what if we wanted to continuously choose a season at random from this list? This is where we can wrap the RANDBETWEEN formula within this by selecting a range of values that matches up with the number of values we are choosing from. For example:
Every time this formula is refreshed, the value changes.
Finally, let’s take a quick look at some considerations when working with the Excel CHOOSE formula that we should keep in mind when using it.
Considerations and watch outs
There are a few things we need to keep in mind when using the CHOOSE formula to make sure we don’t receive any errors.
Firstly, there is a maximum number of values we can work with of 254. For the most part in Excel this should be plenty, but if we try using a range of 255 and above we will not be able to retrieve anything sitting that high.
Second, if we enter an index number that is higher than the number of items in our list we will recieve a #VALUE! error. This is especially important to keep in mind when working with more advanced features such as the random between example used above.
Finally, when selecting items to use in our list of values by selecting cells, we need to choose them one by one – for example C2, C3, C4 and so on. If we try to use a range such as C2:C4 we will receive that same #VALUE! error mentioned above.
This sums up our guide on how to use the Excel CHOOSE function. As we can see the base formula is relatively simple, but allows for quite a bit of added complexity depending on the use case and the other formulas that can be merged with it.
For more handy guides on working with Excel, be sure to check out our Excel Tips page.