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**hide

## Download Practice Workbook

Download the practice workbook from here.

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

## 7 Simple Examples to Use CHOOSE Function with Array in Excel

In this article, we will demonstrate **7** simple examples of using the **CHOOSE** function with **array** in Excel. For describing 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**.

**Read More:** **Advanced Uses of CHOOSE Function in Excel (9 Applications)**

### 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
**delete**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.

### 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 **Names** of some students and their **Marks** in the exam. It also includes the **Marks** distribution of **Grades** 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 **Grades**. 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 satisfies, 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.

**Read More:** **How to Use CHOOSE Function to Perform IF Condition in Excel**

### 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 autofill 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 **left-most** column using **the VLOOKUP function**. For describing 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**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). - It 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
**error**.

- To solve the problem, we will now assign the
**CHOOSE**function into 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 **Names** 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** which ordered the Fruits (**G5:G7**), the **Order Dates**, and **IDs** of the fruits. Suppose, we want to insert the **Fruits** according to their **IDs** (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
**autofill**the rest of the cells,**double-click**on the**plus**sign (see screenshot).

**Read More:** **How to Use CHOOSE Function in Excel for Scenarios**

## 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.

## Conclusion

I hope the above tutorial will be helpful for you to learn 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. Follow our website **ExcelDemy** to get more articles like this.