How to Use the CHOOSE Function in Excel
Here is a comprehensive guide detailing the functionality of the CHOOSE function in Excel and Google Sheets.
Overview
The CHOOSE function is utilized in Excel and Google Sheets to return a value from a list based on a designated position. It effectively allows for selection from a set of choices via a numeric index.
Syntax
The syntax of the CHOOSE function is as follows:
CHOOSE(index_num, value1, [value2], ...)index_num: The position in the list from which to return a value.value1, value2, ...: A list of values from which to select.
Examples
Example 1: Basic Usage
Consider a scenario where we have a list of fruits in cells A1 to A3, and we wish to select a fruit based on its position in the list. We can implement the CHOOSE function as demonstrated:
| A | B |
| 1 | =CHOOSE(2, “Apple”, “Banana”, “Cherry”) |
In this example, choosing index number 2 directs the function to return “Banana”.
Example 2: Using a Cell Reference for Index
A cell reference can be utilized for the index number as well. Assume the index is located in cell B1 and we need to retrieve the corresponding value:
| A | B |
| 1 | 2 |
| 2 | =CHOOSE(B1, “Red”, “Blue”, “Green”) |
Here, the formula returns “Blue,” corresponding to the index specified in cell B1.
Example 3: Error Handling
If the index number falls outside the acceptable range (less than 1 or greater than the number of provided values), the CHOOSE function will yield a #VALUE error. An effective way to manage this is by incorporating the IFERROR function:
=IFERROR(CHOOSE(B1, "Dog", "Cat", "Rabbit"), "Invalid Choice")This formula will display “Invalid Choice” when the index is not within the specified range.
The CHOOSE function enhances flexibility in Excel and Google Sheets by allowing the dynamic selection of values based on their positions within a list.
More information: https://support.microsoft.com/en-us/office/choose-function-fc5c184f-cb62-4ec7-a46e-38653b98f5bc