The `CHOOSE` function in Excel is used to select a value from a list of values based on a specified index number. It can be very handy for selecting from multiple options or generating dynamic results based on a number. Here’s a basic overview of how to use it:
Syntax
CHOOSE(index_num, value1, [value2], ...)
- index_num: This is a number (or a reference to a cell containing a number) that specifies which value to choose. It must be a number between 1 and 254.
- value1, value2, …: These are the values from which `CHOOSE` will select. You can specify up to 254 values. These can be numbers, text, ranges, or cell references.
Example Usage
Suppose you have a need to select a day of the week based on a number:
Example 1: Text Values
- Formula: `=CHOOSE(3, “Monday”, “Tuesday”, “Wednesday”, “Thursday”, “Friday”, “Saturday”, “Sunday”)`
- Result: `Wednesday`
This example selects “Wednesday” because it is the third value in the list, and the `index_num` specified is 3.
Example 2: Cell References
Let’s say you have the numbers 10, 20, 30, and 40 in cells A1 to A4 respectively:
- Formula: `=CHOOSE(2, A1, A2, A3, A4)`
- Result: Corresponds to `A2`, which contains 20, so the result will be `20`.
Example 3: Using CHOOSE with Other Functions
You can also use `CHOOSE` in conjunction with other functions. For example, combining it with `RANDBETWEEN` can help select a random item from a list:
- Formula: `=CHOOSE(RANDBETWEEN(1, 4), “Red”, “Blue”, “Green”, “Yellow”)`
- Result: Randomly selects and displays one of the four colors.
Tips
- Ensure the `index_num` is within the range of the values you have provided. Otherwise, `CHOOSE` will return a `#VALUE!` error.
- It can be extremely useful in nested formulas or when working with dynamic lists.
- While `CHOOSE` works well for small sets of choices, consider using lookup functions like `VLOOKUP` or `INDEX` for more complex situations.
The `CHOOSE` function is a powerful yet simple tool once you get the hang of how index numbers select values.
Unlock Your Potential
Excel
Access
Power BI
