Md. Abdullah Al Murad
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.
- Then, drag the Fill Handle icon to copy the formula up to cell G11.
- You will get the result which is based on our declared criteria.
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.
- Now, drag the Fill Handle icon to copy the formula up to cell F17.
- You will get the price value after providing the discount rate.
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.
- Next, drag the Fill Handle icon to copy the formula up to cell F17.
- 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.
- 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.
- You will get the price of that product in the desired cell.
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.
- 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.
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.
- 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.
- 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.
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
- Advanced Uses of CHOOSE Function
- Use CHOOSE Function with Array
- Apply CHOOSE Function to Create Drop-Down List
- Use CHOOSE Function for Scenarios
- Use CHOOSE Function to Perform IF Condition
- Use RANDBETWEEN with CHOOSE Function
- Use VLOOKUP with CHOOSE Function
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!
