We usually use the **CHOOSE** function to find a value from a list. In this article, we will learn to use the **CHOOSE** function with an array to perform different activities in Excel. Here, array means, we will either input multiple cells in the function or will get multiple results by applying the function.

**Table of Contents**Expand

## Introduction to CHOOSE Function in Excel

The **CHOOSE** function in Excel is created to return a value from a list depending on a given position. We can combine the **CHOOSE** function with other Excel functions for performing various advanced operations.

**Syntax**

`CHOOSE(index_num, value1, [value2], …)`

**Arguments**

* index_num:* It refers to the position where the value is to be returned. It can be any value between

**1**to

**254**, as well as a cell reference or another formula. This is a required argument.

* value1:* It is the first value in the list of

**254**values from which the function chooses. This argument supports numbers, cell references, text values, and defined names or formulas. It is required.

* [value2]:* It refers to the second value in the list of

**254**values from which the function chooses. This argument is optional.

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

In this article, we will demonstrate 7 simple examples of using the **CHOOSE** function with an array in Excel. To describe the methods, we will use some datasets and show the steps with screenshots. So, without further delay, let’s get started.

### 1. Return Single Value in Excel Using CHOOSE Function with Array

Suppose, we have a dataset (**B4:B9**) in Excel that contains the names of some **Participants** in a lottery. Here, the **Serial No.** of the **Champion** is 2 (**E4**). Now, we need to find the **Champion** (**E5**) from the **Participants** list (**B5:B9**) using **the CHOOSE function** in Excel with the array. The steps to do so are below.

**Steps:**

- To begin, go to cell
**E5**. - Next, to find the name of the
**Champion**, type the following formula in cell**E5**:

`=CHOOSE(E4,B5,B6,B7,B8,B9)`

- Then, press the
**Enter**key. - In this way, you will get the name of the
**Champion**in cell**E5**(see screenshot).

In the formula, cell **E4** refers to the position of the value to return. So, 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 inserting a range because the **CHOOSE** function does not support a range.

### 2. Apply CHOOSE Function with Array to Return Multiple Values in Excel

Let’s say, we have a dataset (**B4:D12**) in Excel containing the **Names** of some students and their marks in **Math** and **Physics**. However, we need to pick a student’s **Name** along the marks in the two subjects. Here, we will use the **CHOOSE** function in Excel with the array to return these multiple values. By applying the formula of this method, we will always get the values in a row even if they are in a column. The steps are below.

**Steps:**

- First, select cell
**B12**. - After that, to get the values in a row, enter the formula below in the cell (
**B12**):

`=CHOOSE({1,2,3},B6,C6,D6)`

- Therefore, press the
**Enter**key. - Thus, you will get the values in the range
**B12:D12**(see the picture below).

In the formula, the cells **B6**, **C6** & **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.
- If we select a value, then the other two values will automatically be selected (see screenshot).
- To delete the entire array, you will need to select any one cell in the array (cell B12 in our case).

- Afterward, just press the
**D****elete**button on the keyboard. - As a result, all three values in the array will be removed at once.
- See the final output in the screenshot below.

**Read More: **How to Use Excel Formula to Choose Between Two Values

### 3. Excel CHOOSE Function with Array to Find Values Depending on Specific Conditions

Assuming, we have a dataset (**B4:G10**) in Excel. It contains the **Name**s of some students and their **Marks** in the exam. It also includes the **Marks** distribution of **Grade**s in the range **F4:G10**. Now, we need to determine the **Grades** (**D5:D10**) of the students based on their **Marks** in the exam. Again, we will use the Excel **CHOOSE** function array to find the **Grade**s. Let’s see the steps below.

**Steps:**

- Firstly, activate cell
**D5**. - Secondly, insert the formula below in the 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)`

- After inserting the formula, press the
**Enter**key on the keyboard. - As a result, you will find the
**Grade**(**A+**) in cell**D5**.

🔎 **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)`

Finally, the formula will be:

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

Hence, the output will be the **6th** value (**A+**) in the list.

- At this time, to copy the formula in the rest of the cells put the cursor in the bottom-right corner of cell
**D5**. - Consequently, you will see a
**plus sign**(**+**) there.

- Double-click on the plus sign.
- Eventually, you will find the
**Grades**of the rest of the students (**D6:D10**). - See the final result in the image below.

### 4. Use Array in CHOOSE Function for Doing Calculation in Excel

In this method, we will use the array in the **CHOOSE** function in Excel for doing calculations. For this, we will use the dataset (**B4:D9**) below that contains the names of some **Fruits** and their **Sales**. The worksheet also contains the %** of Sales** (**F5:F7**) for determining the **Estimated Profit** (**D5:D9**) depending on the **Sales** (**G5:G7**). The steps for calculating the **Estimated Profit** using the **CHOOSE** function are below.

**Steps:**

- First of all, we will calculate the
**Estimated Profit**of**Orange**. - To do so, go to cell
**D5**. - Then, type 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)**).

- After that,
**double-click**on the**Fill Handle**to auto-fill the rest of the cells (**D6:D9**). - In this way, you will get the
**Estimated Profit**of all the**Fruits**(see screenshot).

### 5. Apply CHOOSE Function with Array for Left VLOOKUP

In this method, we will apply the **CHOOSE **function with the array for left **VLOOKUP**. We can only search the leftmost column using **the VLOOKUP function**. To describe this method, we will use the dataset (**B4:C9**) below. It contains the **Marks** (**B5:B9**) of some students in the exam and their **Names** (**C5:C9**). Suppose, we want to return the **Marks** of **Joseph** in cell **F5**. Here, we will first use the **VLOOKUP** function and observe the limitation. Then we will combine the CHOOSE function and the VLOOKUP function to fix the limitation. See the steps below.

**Steps:**

- Firstly, we will use the
**VLOOKUP**function to find the**Marks**of**Joseph**. - Hence, activate cell
**F5**. - Next, to get
**Marks**of**Joseph**, enter the following formula:

`=VLOOKUP(F4,B5:B9,1,FALSE)`

- Press the
**Enter**key. - Thereupon, you will see the
**#N/A**error (see screenshot). - This is because the
**VLOOKUP**function can only search for the value in the left-most column. - Here, the lookup column
**B**is located on the right side of the return column**C**. - That is why it shows an error.

- To solve the problem, we will now assign the
**CHOOSE**function to the**VLOOKUP**function. - First of all, we need to delete the previous formula in cell
**F5**. - Next, to get the
**Marks**of**Joseph**, insert the formula below in cell**F5**:

`=VLOOKUP(F4,CHOOSE({1,2},C5:C9,B5:B9),2,FALSE)`

- Finally, press the
**Enter**button. - Thus, you will get the result in cell
**F5**(see screenshot).

🔎 **How Does the Formula Work?**

**CHOOSE({1,2},C5:C9,B5:B9):**It flips the position 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**.

### 6. Combination of Excel SUM & CHOOSE Functions with Array

In this approach, we will combine **the SUM function** and the **CHOOSE** function with an array in Excel. To do so, we will use the dataset (**B4:E8**) below containing the **Name**s of some students and their marks in **Math**, **Physics**, and **Chemistry**. Let’s say, we want to sum up the **Total Marks** of **Joseph** in cell **C11**. The steps are below.

**Steps:**

- Firstly, go to cell
**C11**. - Afterward, to get the
**Total Marks**of**Joseph**, insert the formula below:

`=SUM(CHOOSE(3,C5:E5,C6:E6,C7:E7,C8:E8))`

- Lastly, 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 Marks of Joseph in the three subjects.**SUM(CHOOSE(3,C5:E5,C6:E6,C7:E7,C8:E8)):**Adds the marks returned by the**CHOOSE**function.

### 7. Assign CHOOSE Function with Array to Return a Cell Reference

Here, we will insert the Excel **CHOOSE** function with an array to return a cell reference. For this method, we will use the dataset (**B4:E9**) below that contains the names of some countries that ordered the Fruits (**G5:G7**), the **Order Dates**, and the **IDs** of the fruits. Suppose, we want to insert the **Fruits** according to their **ID**s (positions) in the range **E5:E9**. Follow the steps below to do so.

**Steps:**

- First, select cell
**E5**. - After that, to get the fruit of
**ID = 2**, insert the formula below in cell**E5**:

`=CHOOSE(D5,$G$5,$G$6,$G$7)`

- Then, press
**Enter**. - Consequently, you will find the output (
**Apple**).

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

- To auto-fill the rest of the cells, double-click on the plus sign (see screenshot).

**Read More:** Advanced Uses of CHOOSE Function in Excel

## Things to Remember

While using the **CHOOSE** function, we need to always keep in mind some important things. They are below:

- We can not 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, then the function rounds it to the lower integer.

**Download Practice Workbook**

Download the practice workbook from here.

## Conclusion

I hope the above tutorial will be helpful for you in learning the use of the **CHOOSE** function with the array in Excel. Download the practice workbook and give it a try. Let us know your feedback in the comment section.

## 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
- How to Use RANDBETWEEN with CHOOSE Function in Excel

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