# Use of CHOOSE Function with Array in Excel: 7 Simple Examples

### Example 1 – Return a Single Value Using the CHOOSE Function with an Array

Steps:

• Input the array search number in cell E4.
• Use the following formula in cell E5:
`=CHOOSE(E4,B5,B6,B7,B8,B9)`
• Press the Enter key.

Cell E4 refers to the position of the value to return. Cell E4 is the index_num. Besides, the cells B5, B6, B7, B8, and B9 are the values from which the function will choose the Champion. We inserted the cell names separately instead of a range because the CHOOSE function does not support a range.

### Example 2 – Apply the CHOOSE Function with an Array to Return Multiple Values in Excel

Steps:

• Select cell B12.
• To get the values in a row, enter the formula below in the cell:
`=CHOOSE({1,2,3},B6,C6,D6)`
• Press the Enter key.
• Get the values in the range B12:D12 (see the picture below).

In the formula, the cells B6, C6, and D6 denote the values to be returned. Again, {1,2,3} array refers to the positions of the values. So, {1,2,3} array is the index_num in this formula.

• By applying this formula, we get the result in an array format.
• The other two values will automatically be selected (see screenshot).
• To delete the entire array, you must select a single cell (cell B12 in our case).

• Press the Delete button on the keyboard.
• All three values in the array will be removed at once.

### Example 3 – Excel CHOOSE Function with an Array to Find Values Depending on Specific Conditions

Steps:

• Insert the formula below in cell (D5) to find the Grade of Stephen:
`=CHOOSE((C5>0)+(C5>=40)+(C5>=50)+(C5>=60)+(C5>=70)+(C5>=80),\$F\$5,\$F\$6,\$F\$7,\$F\$8,\$F\$9,\$F\$10)`
• Press the Enter key on the keyboard.

How Does the Formula Work?

• \$F\$5,\$F\$6,\$F\$7,\$F\$8,\$F\$9,\$F\$10: Refer to the list of values to be returned. The ‘\$’ sign is used here to use the formula in other cells. So, we locked these cells using this sign.
• (C5>0)+(C5>=40)+(C5>=50)+(C5>=60)+(C5>=70)+(C5>=80): It is the index_num argument that will analyze the conditions. If the condition is satisfied, it will return TRUE (1) otherwise FALSE (0). In our case, cell C5 meets the six conditions. So the formula becomes:

`=CHOOSE(1+1+1+1+1+1,\$F\$5,\$F\$6,\$F\$7,\$F\$8,\$F\$9,\$F\$10)`

The formula will be:

`=CHOOSE(6,\$F\$5,\$F\$6,\$F\$7,\$F\$8,\$F\$9,\$F\$10)`

The output will be the 6th value (A+) in the list.

• Put the cursor in the bottom-right corner of cell D5.
• You will see a plus sign (+) there.

• Double-click on the plus sign.

### Example 4 – Use an Array in the CHOOSE Function for Calculations

Steps:

• We’ll calculate the Estimated Profit of Orange.
• Go to cell D5.
• Use the following formula in the cell to calculate the Estimated Profit:
`=CHOOSE((C5>0)+(C5>=151)+(C5>=301),C5*\$F\$5,C5*\$F\$6,C5*\$F\$7)`
• After pressing the Enter key, you will find the Estimated Profit of Orange in cell D5.

This formula works in the same way as the previous method. It multiplies the % of Sales (\$F\$5, \$F\$6 & \$F\$7) with the Sales (C5) based on their values ((C5>0)+(C5>=151)+(C5>=301)).

• Double-click on the Fill Handle to auto-fill the rest of the cells (D6:D9).

### Example 5 – Apply the CHOOSE Function with an Array for Left VLOOKUP

Steps:

• We’ll try to get the Marks of Joseph with the following formula:
`=VLOOKUP(F4,B5:B9,1,FALSE)`
• We get an #N/A error (see screenshot). The VLOOKUP function can only search for the value in the left-most column.

• We’ll use the CHOOSE function in the VLOOKUP function to solve this:
`=VLOOKUP(F4,CHOOSE({1,2},C5:C9,B5:B9),2,FALSE)`

How Does the Formula Work?

• CHOOSE({1,2},C5:C9,B5:B9): Flips the positions of column C to column 1 and column B to column 2.
• VLOOKUP(F4,CHOOSE({1,2},C5:C9,B5:B9),2,FALSE): It returns the Marks of Joseph.

### Example 6 – Combination of Excel SUM and CHOOSE Functions with an Array

Steps:

• To get the Total Marks of Joseph, insert the formula below:
`=SUM(CHOOSE(3,C5:E5,C6:E6,C7:E7,C8:E8))`
• Press the Enter key to get the output (see screenshot).

How Does the Formula Work?

• CHOOSE(3,C5:E5,C6:E6,C7:E7,C8:E8): Returns the third array of values.
• SUM(CHOOSE(3,C5:E5,C6:E6,C7:E7,C8:E8)): Adds the marks returned by the CHOOSE function.

### Example 7 – Assign the CHOOSE Function with Array to Return a Cell Reference

Steps:

• To get the fruit of ID = 2, insert the formula below in cell E5:
`=CHOOSE(D5,\$G\$5,\$G\$6,\$G\$7)`
• Press Enter.

In the formula, cell D5 refers to the position number 2. The formula returns the second fruit (Apple) from the list (\$G\$5,\$G\$6,\$G\$7).

• Double-click on the plus sign to auto-fill the rest of the cells (see screenshot).

## Things to Remember

• You can’t use more than 254 values in this function.
• When the index_num is less than 1 or greater than 254, the function returns the #VALUE! error.
• If the entered index_num is a fraction, the function rounds it to the lower integer.

## Related Articles

<< Go Back to Excel CHOOSE Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Sagufta Tarannum

Sagufta Tarannum, holding a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, contributes significantly as an Excel & VBA Content Developer at ExcelDemy. Fueled by a deep interest in research and innovation, she actively engages with Excel. In her role, Sagufta not only skillfully addresses challenging issues but also demonstrates enthusiasm and expertise in gracefully navigating intricate situations, underscoring her unwavering commitment to consistently delivering exceptional content. Her interests are Advanced... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF