While it can be a confusing function to get used to, once you understand the basics, it will become much easier to use. Read on to learn more about how to use the CHOOSE function in Google Sheets.
CHOOSE Function Syntax in Google Sheets
Before we look at examples of the CHOOSE function, let’s look at how it works first. This function requires at least two parameters. However, you can add additional parameters depending on your needs. Here is the syntax for the formula:
Here are the parameters required for the formula to work:
index: This parameter defines the index point from which the function will select the result. This can be up to 29 and should be less than or equal to the number of option parameters. For example, if you have 5 as the index, the function will choose the 5th value in the range. option1: This is the first possible value that can be returned by the function. This can be a string or number enclosed in quotation marks or a reference to a cell within the spreadsheet.
When Should You Use CHOOSE in Google Sheets
The CHOOSE formula in Sheets can find values when you have a small number of them. Think of it as a simpler version of LOOKUP functions, but CHOOSE is much simpler to set up. However, the trade-off is that the CHOOSE function doesn’t work well with a big range of values, as it only supports up to 29.
How to Use the CHOOSE Function in Google Sheets
Now that we know the syntax for the CHOOSE function, let’s look at a few examples of this function in action.
Using a Simple CHOOSE Function
In this example, we will use the most basic version of CHOOSE. This involves writing the index and the options in the formula itself. To do this, follow these steps:
Click on the cell where you wish to enter the formula and enter the initial part of the formula, which is =CHOOSE( Now type in the index parameter, which is the location of the option we wish to choose. Let’s do 2 in this case. Add a comma “,” to separate the parameters. Now, type in the option parameters. In this example, we are doing three of them. It will look something like this: “alpha”,”bravo”,”charlie”. Make sure the option parameters are enclosed in quotation marks unless they’re referencing a cell, and separate them using commas. Add a closing bracket “)” to finish the formula.
The result is bravo, as we used 2 as our index parameter. If we used 1 as the index, the result would have been alpha, and 3 would be charlie.
Use the CHOOSE Function With Cell References
Although the previous example may have helped you understand how the function works, it’s not a great way to use it as it’s very limiting. Let’s look at how you can reference other cells and choose a value. This method allows you to be much more dynamic with your spreadsheet, as you can edit the values in the cells rather than the formula itself.
In the example below, let’s say that a teacher has to assign seven students to the last free slot for PE this semester, one for each sport. So, to be fair, the teacher got the students to pick a number between 1 and 7. Here are the steps they could take to assign sport with the CHOOSE function:
Click on the cell where you want to enter the formula and enter the initial part of the formula, which is =CHOOSE( Type in the index parameter, which is the selection you want to make from the options. For the first cell, B3 as Andy chose 5. Add a comma “,” to separate the parameters. Type in the option parameters. In this example, we are doing seven of them, stored in Column I. Be sure to use absolute references, so the autofill doesn’t get messed up for the remaining cells. The options would look like this: $I$2,$I$3,$I$4,$I$5,$I$6,$I$7,$I$8. Add a closing bracket “)” and press Enter. From there, you can autofill the relevant cells.
Click the following link to learn more about why we had to use absolute references and what they are.
Generating Random Data
You can also use the CHOOSE function by nesting it with other formulas. In this example, we are going to use the data from the previous example, but instead of taking the index parameter from another cell, we are going to use the RANDBETWEEN formula to assign a random sport to each person.
Let’s take a look at how you can do this:
Click on the cell where you wish to enter the formula and enter the initial part of the formula, which is =CHOOSE( In this case, we want the index to be assigned randomly, so we will write RANDBETWEEN(1,7) instead of assigning it. The two parameters used in this formula define the lowest and highest possible values. Add a comma “,” to separate the parameters. Now, type in the option parameters. In the above example, it’s the same options we used before in cells: $I$2,$I$3,$I$4,$I$5,$I$6,$I$7,$I$8. Add a closing bracket “)” and press Enter. Click and drag the fill handle to autofill the rest of the cells.
You’ll notice that each student was randomly assigned a sport in the example above, but there are repeats. So, only use this method if duplicate data doesn’t matter. Alternatively, you could nest the UNIQUE function in Google Sheets into the formula.
Alternatives to CHOOSE in Google Sheets
The CHOOSE formula in Google Sheets allows you to select a value from a given set of values. However, a few other functions will enable you to achieve the same results in your spreadsheet. One of these functions is the INDEX function. It searches for an index result, much like the index parameter of the CHOOSE function does.
Another function that allows you to achieve similar results is the VLOOKUP function (or HLOOKUP). This allows you to vertically look up a specific value in a data set and return the corresponding data point. It also works with ranges rather than typing individual cell references, so it’s often preferred over CHOOSE.
The third function that will allow you to do this is the MATCH function. This function finds the location of a value within a list of defined values in the spreadsheet.
Choosing When to Use This Function
Once you use the CHOOSE function in Google Sheets a few times, it becomes pretty straightforward. But, typing cell references or options individually can be time-consuming.
We’d recommend only using CHOOSE when you have a few parameters. Otherwise, one of the LOOKUP functions could be a better choice.
