How to use the CHOOSE function in Google Sheets

The CHOOSE function allows you to use an index number to choose options specified within the formula. These options can be written within the formula or cell references.

While it can be a confusing feature to get used to, once you understand the basics it will be 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 looking at examples of the CHOOSE function, let’s first see how it works. This function requires at least two parameters. However, you can add additional parameters based on your needs. This is the syntax of the formula:

=CHOOSE(index, option1, [option2, …])

These are the necessary parameters for the formula to work:

• index: This parameter defines the index point from which the function will select the result. It can be up to 29 and must be less than or equal to the number of option parameters. For example, if you have 5 as an index, the function will choose the fifth value in the range.
• Option 1: This is the first possible value that the function can return. It can be a quoted string or number, or a reference to a cell within the spreadsheet.

When should you use PICK 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 the SEARCH functions, but CHOOSE is much simpler to set up. However, the tradeoff is that the CHOOSE function doesn’t work well with a wide range of values, since it only supports up to 29.

How to use the CHOOSE function in Google Sheets

Now that we know the CHOOSE function syntax, let’s see some 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 typing the index and options into the formula itself. To do this, follow these steps:

1. Click in the cell where you want to enter the formula and enter the initial part of the formula, which is =CHOOSE(
2. Now write the index parameter, which is the location of the option we want to choose. Let’s do two in this case.
3. Add a comma “,” to separate the parameters.
4. Now write the option parameters In this example, we are doing three of them. It will look something like this: “alpha”, “bravo”, “charlie”. make sure that option Parameters are enclosed in quotes unless they refer to a cell and separate them with commas.
5. Add a closing bracket “)” to finish the formula.

The result is Bravohow we use two like ours index parameter. if we used 1 as an index, the result would have been alphaY 3 would Charlie.

Use the CHOOSE function with cell references

Although the example above may have helped you understand how the function works, it is not a good way to use it as it is very limiting. Let’s see how you can reference other cells and choose a value. This method allows you to be much more dynamic with your spreadsheet, since you can edit the values ​​in the cells instead of the formula itself.

In the example below, let’s say 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 asked the students to choose a number between 1 and 7. Here are the steps they could take to assign a sport with the PICK function:

1. Click in the cell where you want to enter the formula and enter the initial part of the formula, which is =CHOOSE(
2. Write the index parameter, which is the selection you want to make from the options. For the first cell, B3 as andy chose 5.
3. Add a comma “,” to separate the parameters.
4. Write the option parameters In this example, we are making seven of them, stored in column i. Be sure to use absolute references, so the autofill doesn’t screw 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.
5. Add a closing bracket “)” and press Get into.
6. From there, you can autocomplete the relevant cells.

Click the link below to learn more about why we had to use absolute references and what they are.

Random data generation

You can also use the CHOOSE function by nesting it with other formulas. In this example, we’re going to use the data from the previous example, but instead of taking the index parameter from another cell, we’re going to use the RANDOM BETWEEN formula to assign a random sport to each person.

Let’s take a look at how you can do this:

1. Click in the cell where you want to enter the formula and enter the initial part of the formula, which is =CHOOSE(
2. In this case, we want the index to be assigned randomly, so we’ll write RANDOM BETWEEN(1,7) instead of assigning it. The two parameters used in this formula define the lowest and highest possible values.
3. Add a comma “,” to separate the parameters.
4. Now write the option parameters In the example above, these are the same options we used earlier in the cells: $I$2,$I$3,$I$4,$I$5,$I$6,$I$7,$I$8.
5. Add a closing bracket “)” and press Get into.
6. 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 repetitions. So only use this method if duplicate data doesn’t matter. Alternatively, you can nest the UNIQUE function in Google Sheets in 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, some other features will allow you to achieve the same results in your spreadsheet. One of these functions is the INDEX function. Looks up an index result, just like the index parameter of the CHOOSE function does.

Another function that allows you to achieve similar results is the VLOOKUP (or HLOOKUP) function. This allows you to vertically search for a specific value in a data set and return the corresponding data point. It also works with ranges instead of typing individual cell references, which is why CHOOSE is often preferred.

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 values ​​defined in the spreadsheet.

Choose when to use this feature

Once you use the CHOOSE function in Google Sheets a few times, it becomes pretty easy. However, typing cell references or options individually can be time consuming.

We recommend using CHOOSE only when you have some parameters. If not, one of the SEARCH functions might be a better option.