CHOOSE

The CHOOSE function allows you to select and return a value from a list of values based on a specified index number. It is particularly useful when you want to retrieve a specific value from a list based on a given position or index.

Syntax

=CHOOSE(index_number, value1, value2, ...)

index_number The position of the value to return. Must be an integer between 1 and 254.
value1 value2 ... List of values from which to choose. Can be constants cell references or other formulas.

About CHOOSE

When you find yourself in need of extracting a specific value from a list based on its position, turn to the CHOOSE function in Excel. This function excels at providing flexibility in selecting and returning values based on a numerical index. Whether you’re dealing with categorical data or need to retrieve specific options from a defined list, CHOOSE proves to be a versatile and efficient tool for Excel users.

The CHOOSE function operates by taking an index number and a list of values as arguments. It then retrieves the value corresponding to the provided index from the list of values. This feature presents a convenient method for accessing specific items within a range of options, streamlining data manipulation and analysis within spreadsheets.

A key advantage of the CHOOSE function lies in its straightforward implementation. By simply specifying the index position and providing the list of values, users can dynamically retrieve the desired value without complex or cumbersome calculations.

Furthermore, the CHOOSE function offers flexibility in the types of values it can handle. Whether the list consists of text, numerical data, or even references to other cells or ranges, CHOOSE effortlessly accommodates various types of data, enhancing its applicability across diverse use cases in spreadsheet management and analysis.

Examples

Suppose you have a list of colors in cells A1 to A5 (Red, Blue, Green, Yellow, Orange), and you want to retrieve the color at the third position. The CHOOSE formula would be:

=CHOOSE(3, A1, A2, A3, A4, A5)

This will return 'Green,' which is the color at the third position in the list.

Suppose you have a dataset containing month names in cells B1 to B12, and you want to retrieve the name of the month in the seventh position. The CHOOSE formula would be:

=CHOOSE(7, B1, B2, B3, B4, B5, B6, B7, B8, B9, B10, B11, B12)

This will return the month name at the seventh position in the list.

notes

Questions

Can the CHOOSE function handle non-numeric index numbers?

No, the index_number argument of the CHOOSE function must be a numeric value between 1 and 254. Non-numeric values or index numbers outside this range will result in an error.

What happens if the index number provided to the CHOOSE function is outside the range of available values?

If the index number provided to the CHOOSE function falls outside the range of available values (1 to n, where n is the number of values), the function will return an error.

Can the values in the list provided to the CHOOSE function be dynamic and change based on other conditions?

While the CHOOSE function allows for dynamic values in the list, changing the values may necessitate adjusting the function arguments accordingly to ensure efficient retrieval of the desired value.

Related functions

INDEX
VLOOKUP
HLOOKUP
MATCH
OFFSET