In this article, we will learn the **advanced uses of the CHOOSE function in Excel**. The **CHOOSE function** returns a value from a list using a specified position or index. The values provided to the** CHOOSE **function can be hard-coded constants or cell references. Today, we will demonstrate **9 **advanced applications of the **CHOOSE **function in Excel. We will try to cover all the examples of the **CHOOSE **function. So, without further delay, let’s start the discussion.

## Download Practice Workbook

You can download the workbook from here.

## Introduction to Excel CHOOSE Function

In **Microsoft Excel**, the **CHOOSE **function selects a value or performs an action from a list of values, based on the index number. It needs two required arguments to function.

**Syntax**

**CHOOSE(index_num, value1, [value2]….)**

**Arguments**

** index_num: **It is the position number of the values. Position of

**value1**is

**1**,

**value2**is

**2**, and so on.

** value1: **It is the second required argument inside the

**CHOOSE**function. You can enter

**value2**,

**value3**, and many more for your purposes.

For example, if you type ** =CHOOSE(3,"Jan","Feb","Mar","Apr","May","June") **in a cell, and, press

**Enter**, then the output will be

**Mar**because

**Mar**is the

**third value**among the specified values.

## 9 Advanced & Useful Applications of CHOOSE Function in Excel

In the following section, we will explain **9 **advanced and useful applications of the **CHOOSE **function. To explain the applications, we will use different datasets. We will introduce the datasets while explaining the examples. So, let’s follow the examples below to learn more.

### 1. Use Excel CHOOSE Function to Replace Nested IFs

One of the most advanced and useful applications of the **CHOOSE **function in Excel is that you can use it to replace **Nested IFs**. Generally, when we have multiple criteria or conditions, we use the **Nested IFs **formula. We can use the **CHOOSE **function for the same purpose. Here, we can return a value or a calculation based on conditions. We will demonstrate both in the following subsections.

#### 1.1 Return Value Based on Conditions

In the first case, we will return a value based on some conditions using the **CHOOSE **function. The dataset we will use here contains the **Test Scores **of some students. We can easily assign the grades using the **CHOOSE **function. You can see the **Grade Distribution** in the picture below.

Generally, we use the **Nested IFs **to solve this type of problem. But **Nested IFs **can be confusing sometimes and may encounter problems if the equation becomes large. The **Nested IFs **formula for assigning the grades can be written as below:

`=IF(C5>=90,"A+",IF(C5>=80,"A",IF(C5>=70,"B",IF(C5>=60,"C",IF(C5>=50,"D","F")))))`

Let’s follow the steps below to see how we can use the **CHOOSE **function to replace the **Nested IFs **formula.

**STEPS:**

- First of all, select
**Cell D5**and type the formula below:

`=CHOOSE((C5>0)+(C5>=50)+(C5>=60)+(C5>=70)+(C5>=80)+(C5>=90),"F","D","C","B","A","A+")`

🔎 **How Does the Formula Work?**

**(C5>0)+(C5>=50)+(C5>=60)+(C5>=70)+(C5>=80)+(C5>=90):**In this part of the formula, we have used the**Grade Distribution**as the**index number**inside the**CHOOSE**function. As there are multiple index numbers based on the situation, we have used the**plus**(**+**) operator to make an**OR**operation.**“F”,”D”,”C”,”B”,”A”,”A+”:**This part contains the values corresponding to the index numbers. Depending on the index number, the**CHOOSE**function will return the grades. For example, if the matched index number is between**80**to**89**, then it will print “**A**”.

- Secondly, press
**Enter**and drag the**Fill Handle**down.

- Finally, you will be able to assign the grades based on the test scores.

#### 1.2 Return Calculation Based on Criteria

In the previous example, we returned a value based on some conditions. Similarly, we can also return the result of a calculation using the **CHOOSE **function. To explain the application, we will use a dataset that contains the **Sales Amount **of some **Sellers**. We will extract the **Commission **for each seller. You can also see the **Sales Range **with the corresponding percentage of **Commission**. For example, if the sales amount of a seller is between **$2,001-$2,500**, then he will get a **20**% commission. We will try to find the amount of the **Commission**.

**STEPS:**

- In the first place, select
**Cell D5**and type the formula below:

`=CHOOSE((C5>=0)+(C5>=2001)+(C5>=2501)+(C5>=3001),C5*0.1,C5*0.2,C5*0.3,C5*0.4)`

This formula is similar to the previous one. But it multiplies the value of **Cell C5 **with the **Commission Percentage **based on the index number and then returns the result.

- After that, press
**Enter**and drag down the**Fill Handle**to see the commission for each seller.

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

### 2. Apply CHOOSE Function for Left VLOOKUP in Excel

In Excel, **the VLOOKUP function **looks for a value in the leftmost column of a table and then returns a value in the same row from a column you specify. But in case of multiple criteria, we can’t use the **VLOOKUP **normally. We use the **CHOOSE **function with the **VLOOKUP **function to extract certain values based on multiple criteria. This is another advanced use of the **CHOOSE **function in Excel. Here, we will see the non-array and array formulas for the **VLOOKUP **with **CHOOSE **functions.

To explain both formulas, we will use a dataset that contains the **First Name **and **Last Name **of some employees. We will extract the **Department **based on the names.

#### 2.1 Non-Array Formula

In the first subsection, we will use a non-array formula with the **VLOOKUP **and **CHOOSE **functions. If we use a **Helper **column and use it to constitute a formula, then it will make a non-array formula. Let’s follow the steps to learn more.

**STEPS:**

- Firstly, we need to insert a
**Helper**column on the leftmost side. In our case,**Column B**is the**Helper**column. - Now, we need to concatenate the
**First**and**Last**To do so, select**Cell B5**and type the formula below:

`=C5&D5`

- Press
**Enter**and drag the**Fill Handle**down.

** **

- After that, select
**Cell H6**and type the formula below:

`=VLOOKUP($H$4&$H$5,CHOOSE({1,2},$B$5:$B$11,$E$5:$E$11),2,FALSE)`

Here, the **CHOOSE **function creates a lookup table with **Columns B **& **C**. In this formula, the **VLOOKUP **function looks for the **PeterWilliam **value in **Column B** and returns the corresponding value from the lookup table that the **CHOOSE **function creates.

**Read More:** **How to Use VLOOKUP with CHOOSE Function in Excel**

#### 2.2 Array Formula

If we don’t use any helper column in the case of the **VLOOKUP **function, then the formula becomes an array formula. We can solve the previous example with an array formula. Let’s follow the steps below for more.

**STEPS:**

- In the beginning, select
**Cell G6**and type the formula below:

`=VLOOKUP($G$4&$G$5,CHOOSE({1,2},$B$5:$B$11&$C$5:$C$11,$D$5:$D$11),2,FALSE)`

- After typing the formula, press
**Ctrl**+**Shift**+**Enter**to see the result.

This is an array formula. It looks for the concatenated value of **Cell G4 **and **Cell G5 **in the lookup table. This time the **CHOOSE **function creates a lookup table of two columns. The first column concatenates the value of the **range B5:B11 **and **C5:C11** and the second column stores the **Department **names. Depending on the **First **and **Last **names, it returns the **Department **name.

**Read More:** **How to Use CHOOSE Function with Array in Excel**

### 3. Insert Excel CHOOSE Function to Find Sum of Selected Variables

The application of the **CHOOSE **function inside **the SUM function** is very interesting. We can find the total sales of a month dynamically using the **CHOOSE **function. To explain this example, we will use a dataset that contains the sales amount of a seller for the first three months. We will find the total sales amount for a month in **Cell C10**.

Let’s follow the steps below to learn how we can use the **CHOOSE **function to find the sum of selected variables.

**STEPS:**

- First, select
**Cell C10**and type the formula below:

`=SUM(CHOOSE(B10,B5:B7,C5:C7,D5:D7))`

In this formula, the **CHOOSE **function selects the range based on the month number that we insert in **Cell B10**. If we enter **2 **in **Cell B10**, then the formula becomes:

`=SUM(CHOOSE(2,B5:B7,C5:C7,D5:D7))`

After that, the **CHOOSE **function returns the **range C5:C7 **based on the index number.

So, finally, the formula acts as:

`=SUM(C5:C7)`

That is how we get the total sales for the selected month.

- Hit the
**Enter**key to see the total sales for**February**.

- To see the total sales for
**January**, type**1**in**Cell B10**.

### 4. Assign Day/Month/Quarters to Dates Using Excel CHOOSE Function

Assigning day/month/quarters to dates is another feature of the **CHOOSE **function. For that purpose, we need the help of **the WEEKDAY function**. In the dataset below, you can see the dates of some events. We will try to extract the **Week Day**, **Month**, and **Fiscal Quarters **from them.

Let’s pay attention to the steps below to know more about assigning weekdays, months, and quarters to dates.

**STEPS:**

- In the first place, select
**Cell C5**and type the formula below to assign weekdays to dates:

`=CHOOSE(WEEKDAY(B5),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")`

Here, the **WEEKDAY **function returns the index number for the **CHOOSE **function.

- Press
**Enter**and drag the**Fill Handle**down to see weekdays for all dates.

- Similarly, to assign months to dates, use the formula below in
**Cell D6**:

`=CHOOSE(MONTH(B5), "Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")`

In this formula, the **MONTH **function finds the month number and uses it as the index number.

- Finally, for
**Fiscal Quarters**, you can use the formula below:

`=CHOOSE(MONTH(B5), 3,3,3,4,4,4,1,1,1,2,2,2)`

### 5. Use CHOOSE Function to Return Value Based on Index Number

This is a basic application of the **CHOOSE **function. Generally, it returns a value from the list based on the index number. The index number is the first argument of the **CHOOSE **function. In the dataset below, we have some employees with index numbers. Using the index numbers, we will assign the weekdays for night shifts.

Let’s observe the steps below to learn more about the example.

**STEPS:**

- First, select
**Cell D5**and type the formula below:

`=CHOOSE(C5,"Sun","Mon","Tue","Wed","Thu","Fri","Sat")`

In this formula, **Cell C5 **contains the index number, and based on the index number, the **CHOOSE **function returns a weekday for the night shift.

- After that, press
**Enter**and drag the**Fill Handle**down.

- Finally, you will be able to return a value based on the index number.

### 6. Apply CHOOSE Function for Reverse VLOOKUP in Excel

Usually, the **VLOOKUP **function looks for a value in the leftmost column. But with the help of the **CHOOSE **function, we can make the **VLOOKUP **look for a value in other columns. This can be called the reverse **VLOOKUP **in Excel. For explaining the example, we will use the dataset of **Example 2**. But this time we will find the **First Name **and **Department **of the employee based on the **Last Name**. You can see the **Last Name **is in the middle column of the **range B4:D10**. The reverse **VLOOKUP **will look for the value in the middle column.

Let’s observe the steps below to see how we can use the **CHOOSE **function to make a reverse **VLOOKUP **formula.

**STEPS:**

- First of all, to get the
**First Name**, select**Cell G6**and type the formula below:

`=VLOOKUP($G$4,CHOOSE({1,2,3},$C$5:$C$10,$B$5:$B$10,$D$5:$D$10),2,FALSE)`

- Now, press
**Enter**to see the result.

In this formula, the **CHOOSE **function creates a new table or array that stores the **Last Name **as the leftmost column, the **First Name **as the middle column, and the **Department **as the third column. The **VLOOKUP **formula looks for the value of **Cell G4 **in the new table and returns the desired value from the second column of that table.

- Similarly, use the formula below in
**Cell G7**to get the**Department**of the employee:

`=VLOOKUP($G$4,CHOOSE({1,2,3},$C$5:$C$10,$B$5:$B$10,$D$5:$D$10),3,FALSE)`

### 7. Generate Random Data Using Excel CHOOSE Function

In this example, we will apply the **CHOOSE **function to assign groups to some employees for completing different tasks. But interestingly, we want to assign the groups randomly. For that purpose, we can use **the RANDBETWEEN function**. The **RANDBETWEEN **function returns a series of numbers from the specified range of numbers.

Let’s follow the steps below to assign groups randomly.

**STEPS:**

- To begin with, select
**Cell C5**and type the formula below:

`=CHOOSE(RANDBETWEEN(1,3),"G-1","G-2","G-3")`

In this formula, the **RANDBETWEEN **function generates a random number from **1 **to **3** as the index number of the **CHOOSE **function. Based on the index number, the **CHOOSE **function selects a group from the list.

- In the following step, press
**Enter**and drag down the**Fill Handle**to copy the formula to**Cell C10**.

**Note: **The **RANDBETWEEN **function is a volatile function. So, if you make any changes on the sheet, the assigned groups will automatically change. To make the assigned groups hard-coded, you can paste the values after getting the groups.

**Read More:** **How to Use RANDBETWEEN with CHOOSE Function in Excel**

### 8. Create Formula with CHOOSE Function to Return Next Working Day

Another advanced application of the **CHOOSE **function in Excel is to return the working day. To do so, we need to know the current working day. We will use the dataset below to explain the example. You can see the different current working days of some employees. We will find the next working day using the **WEEKDAY **function with the **CHOOSE **function.

Let’s pay attention to the steps below to learn more.

**STEPS:**

- In the beginning, select
**Cell D5**and type the formula below:

`=C5+CHOOSE(WEEKDAY(TODAY()),1,1,1,1,1,3,2)`

- Press
**Enter**.

The formula may look complex at the first glance. Here, we are adding the value that the **CHOOSE **function returns with **Cell C5**. The **WEEKDAY **function returns a number as the index number of today’s date. Depending on the index number, the **CHOOSE **function selects a value from the list **{1,1,1,1,1,3,2}**. And then the addition happens.

- After that, drag the
**Fill Handle**down to copy the formula.

### 9. Get Total Sales for Specific Store with Excel CHOOSE Function

This example is similar to **Example 3 **but it is another advanced application of the **CHOOSE **function. Here, we will try to get the total sales for a specific store with the **CHOOSE **function in Excel. We will select the store using a drop-down list and the total sales of that store will be visible in **Cell D10**.

Let’s follow the steps to learn the whole application.

**STEPS:**

- Firstly, select
**Cell B10**. - Then, go to the
**Data**tab and select**Data Validation**. It will open the**Data Validation**box.

- Select
**List**in the**Allow**field. - After that, enable editing in the
**Source**field and select the**range B4:B7**. - Click
**OK**to proceed.

- You can now choose the store from the drop-down list.

- In the following step, select
**Cell C10**and type the formula below:

`=IF(B10="Store 1",1,IF(B10="Store 2",2,3))`

- Press
**Enter**.

Here, we have used the **Nested IFs **to assign index numbers for each store. This formula states that if **Cell B10 **contains **Store 1**, then it will print **1**. If it contains **Store 2**, then it will show **2**. Otherwise, it will print **3**.

- Now, select
**Cell D10**and type the formula below:

`=SUM(CHOOSE(C10,B5:B7,C5:C7,D5:D7))`

- Hit
**Enter**.

We have explained the same formula in **Example 3**.

- Finally, if you select
**Store 2**using the drop-down list, you will find the total sales in**Cell D10**.

**Read More:** **How to Apply CHOOSE Function to Create Drop-Down List in Excel**

## Conclusion

In this article, we have **9 **advanced and useful applications of **Excel CHOOSE Function**. I hope this article will help you to perform your tasks efficiently. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Also, you can visit **the ExcelDemy website** for more articles like this. Lastly, if you have any suggestions or queries, feel free to ask in the comment section below.