### 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
**D****elete**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.

**Download the Practice Workbook**

## Related Articles

- How to Apply CHOOSE Function to Create Drop-Down List in Excel
- How to Use CHOOSE Function in Excel for Scenarios
- How to Use CHOOSE Function to Perform IF Condition in Excel

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