Check Sheet Logo
Check Sheet

CHOOSE

The CHOOSE function returns a value from a list of values based on a given index number. The function is commonly used to select a value from a dropdown list or to lookup a value based on its position in a list. The function takes an index number and a list of values as input.

Usage

Use the CHOOSE formula with the syntax shown below, it has 2 required parameters:

=CHOOSE(index, choice1, [choice2, ...])
Parameters:
  1. index (required):
    The index number that determines which value to return. The index number must be between 1 and the number of values in the list.
  2. choice1, choice2, ... (required):
    A list of values to choose from. The list can contain up to 254 values.

Examples

Here are a few example use cases that explain how to use the CHOOSE formula in Google Sheets.

Selecting a value from a dropdown list

The CHOOSE function can be used to select a value from a dropdown list based on an index number. This is useful when creating forms or surveys where users select an option from a list.

Lookup values based on position in a list

The CHOOSE function can be used to lookup a value based on its position in a list. This is useful when working with data that is organized in a table or list.

Creating conditional logic

The CHOOSE function can be used in combination with other functions to create conditional logic. For example, you can use the IF function to determine which index number to use based on a condition, and then use the CHOOSE function to return a value based on the index number.

Common Mistakes

CHOOSE not working? Here are some common mistakes people make when using the CHOOSE Google Sheets Formula:

Missing index argument

One of the most common mistakes is forgetting to provide an index argument. This will result in an error or an incorrect result. Make sure to always include the index argument.

Using non-numeric index

The index argument should be a number that represents the position of the chosen value. Using a non-numeric index will result in an error. Check that the index argument is a number.

Incorrect number of arguments

The CHOOSE formula requires at least two choices as arguments, and can have up to 29 choices. If you include more than 29 arguments, an error will occur. Make sure to include the correct number of arguments.

Mismatched data types

The choices provided to the CHOOSE formula should be of the same data type. If different data types are used, an error may occur or the result may be unexpected. Check that all choices are of the same data type.

Incorrect use of array notation

When using CHOOSE with an array of choices, make sure to use array notation (enclosing the choices in curly braces {}), and not to include the array brackets [] as part of the formula. Including the brackets will result in an error.

The following functions are similar to CHOOSE or are often used with it in a formula:

  • INDEX

    The INDEX formula in Google Sheets is used to return a value or reference of a cell at the intersection of a specified row and column in a given range. The formula is commonly used to lookup and retrieve values from a table or range of cells.

  • VLOOKUP

    The VLOOKUP function is a lookup formula used to search for a value in the first column of a range of cells (the search key) and return a value in the same row from a specified column in that range. This function is most commonly used to look up and retrieve data from a table.

  • HLOOKUP

    The HLOOKUP function is a lookup formula that searches for a key in the top row of a table and returns the value in the same column for a specified row. This function is commonly used to extract data from a table based on a specific criteria.

  • IF

    The IF formula is a logical function used to test a condition and return one value if the condition is true and another value if the condition is false. It is commonly used to create conditional statements and perform calculations based on certain conditions.

Learn More

You can learn more about the CHOOSE Google Sheets function on Google Support.