Did you ever found any function that does not seems useful?
Excel offers functions more than your expectations and each function provides awesome outcomes that you may need. Likewise, CHOOSE function is one of those functions that are effective when combined with other functions. Most likely, CHOOSE function is used to find a value from the list once the position of that value is identified. Today, you will get to know how to use CHOOSE function in Excel with examples.
Syntax of CHOOSE
=CHOOSE(index_num, value1, [value2], …)
The arguments used in this formula are:
Index_num: This is a compulsory argument that simply identifies values to be selected. Index_num argument should be numeric between 1 and 254, or formula, or reference to a cell that has a number between 1 and 254.
Value1, Value2: Value1 is also a compulsory argument, whereas others are optional.
How to Use CHOOSE Function in Excel?
Let’s see how you can use this plain function in Excel with easy-to-understand examples:
Suppose you have a dataset in which there are 6 data points named -2, 3, 10, 24, 8, and 11. Let’s consider you need to choose the 4th value.
For this, the formula would be:
“=CHOOSE(4,2,3,10,24,8,11)”
Or
“=CHOOSE(4,A3,A4,A5,A6,A7,A8)”
You will get a value of 24. When the index value is A4, you will get 10. It simply means that A4 matches 3. Similarly, the 3rd value would be A5, and 10.
Do you get the point?
This is a simple procedure that guides how to use CHOOSE function in Excel. Let’s have a look at another example.
Example 2: How to Use CHOOSE Function in Excel?
Suppose you are having columns that have a list of colors, flowers, and numbers. And from this data, you need to find a value from the array of values. You want to choose the 3rd value.
Below is the CHOOSE formula you can use:
“=CHOOSE(3,B4:B9,C4:C9,D4:D9)”
“D4:D9” is the 3rd value list, which is greater than or equal to the input values of column D. Therefore, the formula values would be the same as the list of values of “D4:D9”.
When you have a single cell, you will notice only a single value will be returned as a result of the list. Also, bear in mind that the cell position determines the selection in which you need the outcome. Look at the F4 cell, the formula used is:
“=CHOOSE(3,B4:B9,C4:C9,D4:D9)”
It returns the value 8 which matches the value in cell D4.
Likewise, the F5 cell also has the same result 11 that matches the value in cell D5.
Important Notes:
- Remember that if index_num is 1, CHOOSE value will also be 1, if index_num is 2, CHOOSE will also have 2, and so on.
- You have to enter Value1 and Value2 as individual values.
- If you find the index_num is a fraction, it will be minimized to the lowest integer before using it.
- If the index_num is an array, all values are calculated when CHOOSE is calculated.
- The value in the formula can be a range reference or a single value.
CHOOSE Function Uses:
You will find CHOOSE function returning a particular value, such as:
- Evaluate the next Sunday depending on the beginning date.
- When it is the month number, you will get the fiscal quarter number.
- For the VLOOKUP function, you need to merge columns in a different order.
- Display a sum of sales when you have store numbers.
Things to Consider while Using Excel CHOOSE Function:
You must realize that CHOOSE is not such a function that can handle major issues you may encounter while working on the sheet. This simple function is not responsible for the results that come up against your expectations. These unexpected results could be because of several reasons, such as:
- 254 is the limit to add number values.
- If the index_num argument in the formula is a fraction, it is minimized to the lowest integer value.
- If index_num is less than 1 or greater than the values in the list, the #VALUE! the error will appear.
Errors Occur while Using CHOOSE Function:
As you already know CHOOSE function is not so powerful to deal with difficulties, therefore chances are you will encounter errors more than you expect.
Below are some errors you will face while using this function:
The #VALUE! error will occur, if:
- The index_num argument does not match the number value.
- The index_num argument is greater than the numeric values to choose from.
The #NAME! error will occur, if:
- Valid cell references don’t appear as arguments.
- The value arguments in the formula are provided as text arguments other than quotes.
Summary:
In this post, you have learned how to use CHOOSE function in Excel with multiple examples for a better understanding. Practice each method and you will learn it.
