How to use the CHOOSE function
The CHOOSE function lets you get a value based on a number, the number determines which value to get. The first value is 1 and the second value is 2 and so on.
Formula in cell E3:
The CHOOSE function demonstrated above uses the specified number in cell D3 to get a value from cells B3, B4, and B5. Number two returns the value in cell B4. Cell B4 is the second cell reference.
Note, you can't use a cell range containing multiple values. You need to type each cell separated by a comma. There is an exception to this demonstrated later in this article.
Table of Contents
- CHOOSE function Syntax
- CHOOSE function arguments
- CHOOSE function example
- How to hardcode values in the CHOOSE function
- CHOOSE function array
- CHOOSE function alternative
- CHOOSE function drop-down list
- CHOOSE function with VLOOKUP
- CHOOSE function return array
- CHOOSE function from list
- Get Excel *.xlsx file
1. CHOOSE function Syntax
2. CHOOSE function Arguments
| Argument | Text |
| Index_num | Determines which value is chosen. This argument is required, you can use a number between 1 and 254. |
| value1, value2, value3 | Up to 254 values Index_num can pick from. The first value is required the remaining values are optional. |
3. CHOOSE function example
The picture above displays a formula that allows you to select a cell range and then a SUM function adds all numbers in the selected cell range.
Formula in cell C3 selects cell range C8:C10Â and adds the numbers 300 + 400 + 500 = 1200 is shown in cell C3.
Array formula in cell C3:
3.1 How to enter an array formula
- Copy the array formula above.
- Double press with the left mouse button on cell C3, a prompt appears.
- Paste it to cell C3, shortcut keys are CTRL + v.
- Press and hold CTRL + SHIFT keys simultaneously.
- Press Enter once.
- Release all keys.
The formula bar shows a beginning and ending curly bracket, don't enter these characters yourself.
They appear automatically if you followed the above steps.
3.2 Explaining formula
Step 1 - Choose cell reference
CHOOSE(B3, B8:B11, C8:C11, D8:D11)
becomes
CHOOSE(2, B8:B11, C8:C11, D8:D11)
becomes
C8:C10
and returns {300; 400; 500}. Cell reference C8:C10 is the second cell reference.
Step 2 - Add numbers and return a total
The SUM function calculates a sum based on a given set of numbers. It works fine with arrays as well.
SUM(number1, [number2], ...)
SUM(CHOOSE(B3, B8:B11, C8:C11, D8:D11))
becomes
SUM({300; 400; 500})
and returns 1200 in cell C3.
4. How to hardcode values in the CHOOSE function
Use function key F9 to quickly create hard-coded values from a specific cell range, then use the values in the CHOOSE function.
- Double-press with left mouse button on an empty cell.
- Type = (equal sign)
- Select a cell range with your mouse or type a cell range.
- Press F9 to convert values in cell range to "constants" or hard-coded values.
- Delete the curly brackets { }.
- Replace semicolons with colons.
- Use the values in your CHOOSE function.
5. CHOOSE function array
The CHOOSE function can't work with arrays or cell ranges, it returns a #VALUE error if you try.
Formula in cell C3:
What can you do about it? Use the INDEX function, see the example below.
6. CHOOSE function alternative
Formula in cell C3:
The INDEX function has the following arguments:
INDEX(array, [row_num], [col_num], [area_num])
Explaining formula in cell C3
INDEX({"Red";"Blue";"Green"},B3)
becomes
INDEX({"Red";"Blue";"Green"},2)
and returns "Blue" in cell C3. "Blue" is the second value in the array.
7. CHOOSE function drop-down list
The image above shows a drop-down list in cell C4, it is populated with values from a given column in B8:D12 based on the number specified in cell B3.
For example, cell B3 contains 2. The drop-down list is populated with values from the second column. Change the number to 3 in cell B3 and the drop-down list is instantly and automatically populated with values from column 3.
Drop-down list formula:
7.1 How to insert a drop-down list
- Go to tab "Data" on the ribbon.
- Press with the left mouse button on button "Data Validation", a dialog box appears.
- Press with the left mouse button on drop-down list "below "Allow:" and change it to "List".
- Press with left mouse button on in the field below "Source" and type the following formula:
=CHOOSE(B3,B8:B11,C8:C10,D8:D12)
- Press with the left mouse button on "OK" button.
8. CHOOSE function with VLOOKUP
This picture above demonstrates a VLOOKUP function combined with the CHOOSE function, the CHOOSE function lets you use two different data sets in B7:E11 and B15:E18. Cell C3 contains a number that determines which data set to be used.
Formula in cell E3:
8.1 Explaining formula
Step 1 - Choose cell reference
CHOOSE(C3, B7:E11, B15:E18)
becomes
CHOOSE(2, B7:E11, B15:E18)
and returns B15:E18.
Step 2 - Find value in the chosen data set
The VLOOKUP function lets you search the leftmost column for a value and return another value on the same row in a column you specify.
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
VLOOKUP(B3, CHOOSE(C3, B7:E11, B15:E18), D3, FALSE)
becomes
VLOOKUP(B3, B15:E18, D3, FALSE)
becomes
VLOOKUP("C", B15:E18, 3, FALSE)
and returns the value in D18. "C" is found on row 18 in the leftmost column in B15:E18. The third argument col_index_num is 3. The intersection of row 18 and the third column is D18.
9. CHOOSE function return array
The image above demonstrates how to return an array of values using the CHOOSE function.
Array formula in cell D3:
Excel 365 users can enter this formula as a regular formula, previous Excel versions need to enter this as an array formula.
9.1 Explaining formula
CHOOSE(B3,{2;3;1}, {4;2;6})
becomes
CHOOSE(2,{2;3;1}, {4;2;6})
and returns {4;2;6}. The CHOOSE function returns the second array.
This array has a semicolon as a delimiting character, this means that the values are vertically arranged in the array. This is why you get an array of values in column D, see the image above.
10. CHOOSE function from list
The picture above shows the CHOOSE function in cell F3, one disadvantage is that you need to press with left mouse button on each cell in the list to build the formula.
If you have a long list that will take some time, however, there is a workaround.
You can quickly convert a cell range to hard-coded values, here are the steps.
- Double press with left mouse button on a cell
- Type =CHOOSE(E3,TRANSPOSE(C3:C12))
- Select TRANSPOSE(C3:C12)Â in the formula with your mouse
- Press function key F9 to convert the cell reference to values
- Delete the curly brackets { }
- Press Enter
Note, you don't need the TRANSPOSE function if your values are arranged horizontally.
However, the CHOOSE function allows you to have up to 254 arguments, and perhaps hardcoded values are not what you want. If you have more than 254 values you need another solution, demonstrated below.
The INDEX function allows you to choose a value from a cell range without the need to select each value while building the formula.
Useful links
Excel CHOOSE function with formula examples
CHOOSE function - Microsoft
'CHOOSE' function examples
The picture above shows the CHOOSE function in cell F3, one disadvantage is that you need to press with left […]
This article demonstrates how to highlight a group of bars in a chart bar, the techniques shown here works fine […]
This article demonstrates two formulas, the first formula counts items by quarter and the second formula extracts the corresponding items […]
Functions in 'Lookup and reference' category
The CHOOSE function function is one of many functions in the 'Lookup and reference' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form