…
Examples.
| Data | ||
|---|---|---|
| Formula | Description | Result |
| =CHOOSE(2,A2,A3,A4,A5) | Value of the second list argument (value of cell A3) | 2nd |
One of those Excel functions, CHOOSE, may not seem useful on its own, but when combined with other functions, it offers a ton of fantastic advantages. The CHOOSE function’s most fundamental use is to retrieve a value from a list by specifying its position. There are a number of advanced uses that will be covered later in this tutorial that are definitely worth exploring.
How to use the CHOOSE function
How to use the CHOOSE function in Excel
Heres how to use the CHOOSE function in Excel:
1. Insert the CHOOSE function
Choose the cell that will contain the returned value. Type “=CHOOSE” and press “Enter” on your keyboard. Additionally, you can access this feature by selecting the “Formulas” tab. Choose by selecting “Lookup & Reference” under the “Function Library” section. “.
2. Type your list of values
The index value is the first argument after “=CHOOSE(,” but it’s frequently simpler to type the values you want the function to return first. Therefore, after “=CHOOSE(” add a comma with your cursor there to make the first argument empty for the time being. Then, type the values you want the function to return. Place each value in quotes and divide it with commas if you want the function to return numbers or letters. For instance, your function would look like this: “=CHOOSE(, “red”, “blue”, “orange”) if you wanted it to return red, blue, and orange. “.
Use cell references as the values if you want the function to return a value that is contained in a cell. You can click on each cell while depressing the “Crtl” or “” key. This action automatically includes a comma after each cell reference. Make sure the cell references are absolute if you want the function to refer to the same cells regardless of where it appears in the spreadsheet. After entering the cell reference in the function, you can do this by pressing “F4” on your keyboard.
3. Enter the index value
After entering your list of values, click on the area you left empty for the first argument to enter the index value. View your list of values and enter the number that reflects the position of the values you want. Because blue is the second value on the list, the function would look like this: “=CHOOSE(2, “red”, “blue”, “orange”)” if you wanted it to return “blue.”
Keep in mind that cell references can serve as the index value. “=CHOOSE(A1, “red,” “blue,” “orange”) is an example of a function that uses a cell reference as the index value. If the value in cell A1 is 1, the function would return “red.” “The function changes based on the value in A1.” For instance, the function would return “orange” if you entered “3” in A1. ” Press “Enter” to finalize the function.
What is the CHOOSE function in Excel?
In Excel, the CHOOSE function is a lookup formula that returns one of the values you specify. Each value you enter receives an integer according to the order they appear. For instance, the function returns the integers 1, 2, and 3, respectively, for the values “red,” “blue,” and “orange.” Based on the function’s integer index value, CHOOSE returns a single value. If the integer index value in the preceding example is 1, the function would return “red.”
The CHOOSE function can be used for a variety of things, including giving dates quarters and indicating ratings based on numerical scores. Due to its simplicity, people might favor the CHOOSE function over other lookup formulas. For instance, the CHOOSE function, unlike the VLOOKUP function, doesn’t need a reference table because the values are entered directly into the function. Similar outcomes can be obtained using the CHOOSE function as opposed to more intricate nested IF functions.
Example of the CHOOSE function in Excel
Here is an illustration of how you might employ Excel’s CHOOSE function:
A T-shirt manufacturer reviews an order sheet. The manufacturer wants to create a column that lists the sizes in words but the computer automatically generates a number based on the T-shirt’s size. The computer uses the following codes to indicate size:
Heres what the spreadsheet looks like so far:
The manufacturer decides to use the CHOOSE function to return the color of each T-shirt. ABCOrder number**Color code**Color11564981215649953156500441565014 He enters the formula “=CHOOSE (B1, “XS”, “S”, “M”, “L”, “XL”) in cell C1. Because B1 has a value of 1, C1 returns “XS” after you press “Enter.” This function, which returns the values “XL,” “L,” and “L,” is copied by the manufacturer to C2, C3, and C4. “.
Tips for using the CHOOSE function in Excel
Here are some pointers for using Excel’s CHOOSE function:
Use nonintegers as index values
It might occasionally be necessary to set the function’s index value to a noninteger. You may do this, but keep in mind that the function rounds down by default. For instance, consider this function: “=CHOOSE(C3, “10”, “20”, “30”). ” If the value in C3 is 2. 6 is rounded down to 2, which the CHOOSE function uses as the index value. As a result, since 20 is the second item on your list, it returns 20.
Create a dynamic key
If you intend to frequently alter a function’s output values, think about using CHOOSE to create a dynamic key. For instance, envision yourself as a manager who wants to rate employees based on their performance. Your key may be displayed as follows at the bottom of your spreadsheet:
You can enter the names of the employees in a separate section of your spreadsheet and use CHOOSE to generate ratings based on their scores. ABScore**Rating**11Poor22Fair33Good44Excellent Absolute cell references are used as values in the function to indicate that you should always use the key values. The function would appear as follows if the desired index value were to be found in cell D10:
=CHOOSE(D10, $B$1, $B$2, $B$3, $B$4)
The function updates itself when you alter the rating name in the key. For instance, any function with an integer value of 1 would now return “Needs Improvement” if you changed “Poor” to “Needs Improvement”. “.
Return quarter numbers based on dates
To return quarter numbers based on dates, CHOOSE is frequently used by finance professionals. Making use of the MONTH function as the index value is a practical way to achieve this. It produces an integer value that can be used by the CHOOSE function to identify which quarter corresponds to the month. Consider using this function to fill in the quarter for June, for example. The CHOOSE function recognizes that June is the sixth month because it receives the MONTH function as its first argument. Then, because June is in the second quarter, the CHOOSE function returns the sixth value in your list of values, which is two.
Please note that Indeed is not affiliated with any of the businesses mentioned in this article.
FAQ
How do I use the Choose function in Excel using VLOOKUP?
Then, using an approximate match, VLOOKUP returns the value from the second column in that array (C3:C7). Choose the CHOOSE function from the formula bar to view the array it generates. To evaluate that component of the formula, press the F9 key.
What is the syntax of Choose function?
The CHOOSE function has the following syntax: CHOOSE(index_num, value1, [value2],…) Where Index_num is a required parameter and indicates the position of the value to return. Any number between 1 and 254 is acceptable, as well as a cell reference or another formula.
How do I use the Choose function in Excel VBA?
The VBA Choose function chooses the corresponding value from a list of arguments for a given index. The syntax of the function is: Choose( Index, [Choice-1], [Choice-2], . The index of the value you wish to return, where n is the total number of possible values and must fall between 1 and
How do I select a cell by value in Excel?
- Click on a cell to select it. Alternately, locate it using the keyboard and choose it.
- Select a cell, then while holding down the left mouse button, drag it over the other cells to select a range.
- Hold Ctrl while selecting the cells to select non-adjacent cells and cell ranges.
