How to Use CHOOSE Function in Excel (4 Ideal Examples)

Get FREE Advanced Excel Exercises with Solutions!

MS Excelโ€™s CHOOSE function returns a value from a list using a given position or index. The values provided to CHOOSE can be hard-coded constants or cell references. This is one of those Excel functions that may not seem useful on their own but combined with other functions gives several remarkable advantages. This article will share the complete idea of how the CHOOSE function works in Excel independently and then with other Excel functions with 6 ideal examples. If you are also curious to know, download our practice workbook and follow us.


Download Practice Workbook

Download this practice workbook for practice while you are reading this article.


Introduction to CHOOSE Function in Excel

Function Objective:

Chooses a value or action to perform from a list of values, based on an index number.

Syntax:

=CHOOSE (index_num, value1, [value2], โ€ฆ)

Arguments Explanation:

Argument Required or Optional Value
index_num Required This is the argument that can be a text string or a variable that holds a text string.
value1
Required Pass the first value from which to choose.
[value2]
OPTIONAL Pass the second value from which to choose.

Return Parameter:

Return our desired value on the basis of our index number.


6 Ideal Examples to Use CHOOSE Function in Excel

In this article, we will show you how to use the CHOOSE function with 6 suitable examples. To demonstrate the examples, we consider different types of datasets.

๐Ÿ“š Note:

All the operations of this article are accomplished by using Microsoft Office 365 application.


1. Return Several Values Based on Condition

In this example, we consider a dataset of 7 students and their marks for 3 different courses and total. So, the dataset is in the range of cells B5:F11. Our criteria are in the range of C14:D20.

The application of the CHOOSE function will be in column G. The steps to complete this example are given below:

๐Ÿ“Œ Steps:

  • First of all, select cell G5.
  • Now, write down the following formula in the cell.

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

  • Press Enter.

Applying CHOOSE Function to Return Several Values Based on Condition

  • Then, drag the Fill Handle icon to copy the formula up to cell G11.

Use the Fill Handle icon to copy formula

  • You will get the result which is based on our declared criteria.

Return Several Values Based on Condition by CHOOSE Function

Thus, we can say that our formula works perfectly, and we are able to return values based on our condition by the CHOOSE function in Excel.

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


2. Do Various Calculations Based on Conditions

In the following example, we are going to perform some calculations using the CHOOSE function. For that, we consider a dataset of products in the range of cells B6:E17. Here, we want to provide some discount to the consumer on the price basis of the corresponding products. The discount condition is in the range of cells B20:C24.

The steps to finish this example are given as follows:

๐Ÿ“Œ Steps:

  • First, select cell F6.
  • After that, write down the following formula in the cell.

=CHOOSE((E6>0) + (E6>=651) + (E6>=801) + (E6>=1001) + (E6>=1101),"No discount ",E6*5%, E6*8%, E6*10%,E6*12%)

  • Then, press Enter.

Do Various Calculations Based on Conditions by the CHOOSE Function

  • Now, drag the Fill Handle icon to copy the formula up to cell F17.

Copy formula by dragging Fill Handle icon

  • You will get the price value after providing the discount rate.

Use of CHOOSE Function to Do Various Calculations Based on Conditions

Hence, we can say that our formula works effectively, and we are able to estimate the final product price using the CHOOSE function.


3. Generate Random Data

Now, we are going to provide the discount rate on our product randomly. To show the procedure, we are going to use the previous dataset. Besides the CHOOSE function, we will also use the RANDBETEWEEN function.

The steps to accomplish this example are explained below:

๐Ÿ“Œ Steps:

  • At first, select cell F6.
  • Afterward, write down the following formula in the cell.

=CHOOSE(RANDBETWEEN(1,5),"No Discount","5%", "8%", "10%", "12%")

  • Press Enter.

Generating Random Data by the CHOOSE Function

  • Next, drag the Fill Handle icon to copy the formula up to cell F17.

Copying the formula up to last cell

  • You will figure out the random discount rate in column F, which may be applicable to the product price.

Therefore, we can say that our formula works precisely, and we are able to find out the discount rate through the CHOOSE function.

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


4. Use CHOOSE Formula to Do a Left VLOOKUP

In this case, we will use the combined formula of the CHOOSE and VLOOKUP functions to get our desired data. We are going to use our product dataset to demonstrate the procedure, and we want to get the price of our desired model.

The steps of this approach are shown as follows:

๐Ÿ“Œ Steps:

  • Firstly, select cell D21.
  • Next, write down the following formula in the cell.

=VLOOKUP(D20,CHOOSE({1,2}, D6:D17, E6:E17),2,FALSE)

  • Press Enter.

 Apply CHOOSE Formula to Do a Left VLOOKUP operation

  • You may find out a #N/A error inside the cell.
  • After that, write down the model name of the product in cell D20. Here, we write down GALAXY NOTE 9 to complete the process.
  • Press Enter.

Entering our desired model

  • You will get the price of that product in the desired cell.

Getting desired product price by the CHOOSE function

In the end, we can say that our formula works fruitfully, and we are able to find out the discount rate through the CHOOSE function.

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


5. Apply CHOOSE Formula to Return to Next Working Day

In this example, we will use the CHOOSE, WEEKDAY, and TODAY functions to determine the next working day. We have a dataset in the range of cells B5:C12.

The process is described below step-by-step:

๐Ÿ“Œ Steps:

  • In the beginning, select cell B5.
  • Now, write down the following formula in the cell.

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

  • After that, press Enter.

Application of CHOOSE Formula to Return to Next Working Day

  • Then, drag the Fill Handle icon to copy the formula up to cell D12.

  • You will get the next working day date in column D.

CHOOSE Function to Return to Next Working Day in Excel

At last, we can say that our formula works properly, and we are able to get our desired result by the combined formula of the CHOOSE, WEEKDAY, and TODAY functions.

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


6. Return a Custom Day or Month Name from a Date

In the last example, we will show the weekday and the month name using the CHOOSE function. The result will be shown in columns G and H.

The procedure is explained below step-by-step:

๐Ÿ“Œ Steps:

  • At the start, select cell G5.
  • Then, write down the following formula in the cell.

=CHOOSE(WEEKDAY(F6),"Sun","Mon","Tues","Wed","Thurs","Fri","Satur")

  • Next, press Enter.

Use the CHOOSE function to get weekday name

  • Similarly, select cell H5 and write down the following formula in the cell.

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

  • Again, press Enter.

Utilizing the CHOOSE function for month name

  • Now, select the range of cells G5:H5 and drag the Fill Handle icon to copy the formula up to cell H17.

  • You will get the weekday and month names in the desired columns.

Return a Custom Day or Month Name from a Date

Finally, we can say that we can use the CHOOSE function in Excel and get the weekday and month names in the desired area.


Things to Remember

Common Errors When they show
#VALUE If index_num is out of range, CHOOSE will return #VALUE.
Range or Array Problem CHOOSE will not retrieve values from a range or array constant.

Excel Choose Function: Knowledge Hub


Conclusion

Thatโ€™s the end of this article. I hope that this article will be helpful for you and you will be able to use the CHOOSE function in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.

Donโ€™t forget to check our website, ExcelDemy, for several Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Md. Abdullah Al Murad

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo