Advanced Uses of CHOOSE Function in Excel (9 Applications)

In this article, we will learn the advanced uses of the CHOOSE function in Excel. It 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 this function. So, without further delay, let’s start the discussion.

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

CHOOSE Function in Excel: 9 Advanced & Useful Applications

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:

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:

• 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:

• 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 Name and Last Name. To do so, select Cell B5 and type the formula below:

• Press Enter and drag the Fill Handle down.

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

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:

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

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:

• 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:

• 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:

• Finally, for fiscal quarters, you can use the formula below:

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:

• 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. To explain 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:

• Now, press Enter to see the result.

• Similarly, use the formula below in cell G7 to get the Department of the employee:

7. Generate Random Data Using Excel CHOOSE Function

In this example, we will apply the CHOOSE function to assign groups to some employees to complete 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:

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

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:

• Press Enter.

• 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:

• Press Enter.

• Now, select cell D10 and type the formula below:

• Hit Enter.

• 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

Download Practice Workbook

You can download the workbook from here.

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.

Related Articles

• How to Use Excel Formula to Choose Between Two Values
• How to Use CHOOSE Function in Excel for Scenarios

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