How to Use CHOOSE Function in Excel?

Get FREE Advanced Excel Exercises with Solutions!

Here we will learn how to use the Excel CHOOSE function and execute unique examples of using this function. We will use the CHOOSE function to get the sum of numbers, create a chart, and get specific data from the selected range. The CHOOSE function is available in Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, and Excel 365.

Using the CHOOSE function is quite common and a basic part of Excel. If you are working on a huge dataset and need some specific category to work with, then the CHOOSE function is very useful. The CHOOSE function returns the exact value corresponding to the index number so that you will get the accurate value quickly.

The overview image is shown below. Once you go through the total article you will get the proper idea about the CHOOSE function.

Overview image of choose function


How to Use CHOOSE Function in Excel: 7 Simple Examples

To use the CHOOSE function in Excel there are different ways. Here we will practice unique examples of using the CHOOSE function. Here you can see the overview image of the CHOOSE function. Click on the image to get better visualization and better understanding.

CHOOSE function overview

Example 1: Using CHOOSE Function to Get Data from Selected Range

In this example, we will use the CHOOSE function to select particular data in Excel. Here, the CHOOSE function will return the value from the list at a specific position. For instance, if you select a range of student IDs and write 8, then this function will return the value of the 5th position.

=CHOOSE(C14,$C$5,$C$6,$C$7,$C$8,$C$9,$C$10,$C$11,$C$12)
  • In this formula, cell C14 is the index number. You will get the position of the output from the index number, and the rest of the cells are values to get the output as required.

Using Excel CHOOSE function to get data from selected range


Example 2: Applying the CHOOSE Function to Get the Sum

Here, we will get the sum of marks for all the subjects using the CHOOSE and the SUM function.

=SUM(CHOOSE(C14,$D$5:$F$5,$D$6:$F$6,$D$7:$F$7,$D$8:$F$8,$D$9:$F$9,$D$10:$F$10,$D$11:$F$11,$D$12:$F$12))
  • Index number cell C14 will return the value of the nth position (till 254), and the selected ranges are the values. This function will return the sum value of the selected range according to the Index Number.

Applying Excel CHOOSE function to get the sum

Formula Breakdown

SUM()

  • The sum function sums up the lookup range used in the formula.

SUM(CHOOSE(C14,$D$5:$F$5,$D$6:$F$6,$D$7:$F$7,$D$8:$F$8,$D$9:$F$9,$D$10:$F$10,$D$11:$F$11,$D$12:$F$12))

  • The CHOOSE function chooses the required value and then the SUM function sums up the data as output.

Example 3: Using the CHOOSE Function to Get a Specific Range

In this example, we will get criteria-based data. Suppose we need the highest and lowest marks of the students, so we can apply the CHOOSE function using criteria in a specific range.

  • First, select cell E14 to apply the formula which is based on the criteria.
=CHOOSE($B$19,"3 Highest Total","3 Lowest Total")

Using Excel CHOOSE function to get a specific range

  • Here, select cell B19 to apply for the index number and lock the cell.
  • Then, write the criteria and complete the formula.
  • After that, write another formula using the CHOOSE function, the LARGE function, and the SMALL function to get the values of the selected criteria as below.
=CHOOSE($B$19,LARGE($F$5:$F$12,D15),SMALL($F$5:$F$12,D15))
  • Last, drag down the Fill handle to get all three highest or lowest values.

Getting highest and lowest numbers

Formula Breakdown

SMALL($F$5:$F$12,D15)

  • $F$5:$F$12 range will find out the specific value according to the index number in cell D15 and return the value as the lowest number.

LARGE($F$5:$F$12,D15)

  • The range $F$5:$F$12 will find the value according to the cell D15 and return the highest marks as criteria.

CHOOSE($B$19,LARGE($F$5:$F$12,D15),SMALL($F$5:$F$12,D15))

  • Here, the CHOOSE function will return the three highest or three lowest values according to the criteria in cell B19.


Example 4: Utilizing CHOOSE Function as a Reminder (Custom Day/Month)

In this example, we will use the CHOOSE function along with the WEEKDAY function and the MONTH function so that we can get the day and month names from the date.

  • Initially, enter the formula to get the days in cell D5.
=CHOOSE(WEEKDAY(C5),"Saturday","Sunday","Monday","Tuesday","Wednesday","Thursday","Friday")

Utilizing Excel CHOOSE function as a reminder

  • The CHOOSE function will return the selected date and the WEEKDAYS function will return the day of the selected date.
  • Now, get the month name with the MONTH function using the same process already used in “Weekday”.
=CHOOSE(MONTH(C5),"January","February","March","April","May","June","July","August","September","October","November","December")

Getting the month name

Read More: Advanced Uses of CHOOSE Function in Excel


Example 5: Using CHOOSE Function to Get Value from Specific Lookup Range

In this example, we will use the VLOOKUP function and the CHOOSE function to get the required data from a specific lookup range.

  • Select cell C15 to enter the function and complete the process.
=VLOOKUP(C14,CHOOSE({2,1}, C5:C12, B5:B12),2,FALSE)

Using VLOOKUP with CHOOSE function

Formula Breakdown

CHOOSE({2,1}, C5:C12, B5:B12

  • Here, {2,1} shows that column C is the 2nd column and column B is the first column and CHOOSE function selects the value from these to the column.

VLOOKUP(C14,CHOOSE({2,1}, C5:C12, B5:B12),2,FALSE)

  • The VLOOKUP function searches the value from the lookup range and provides the required output as shown.

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


Example 6: Applying RANDBETWEEN Function to Get Random Values

Here, we will select the random value using the CHOOSE function and the RANDBETWEEN function.

=CHOOSE(RANDBETWEEN(1,3),"Better","Good","Satisfactory")
  • The RANDBETWWEN function returns a random value here, and the CHOOSE function chooses the value to get the required output.

Applying RANDBETWEEN with CHOOSE function to get random value

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


Example 7: Utilizing CHOOSE Function to Get a Specific Range from Chart

In this example, we will utilize the CHOOSE function to get a specific range from a chart.

  • Initially, create a chart (go to Insert >> Chart) selecting Student ID as X-axis. and the subjects as Y-axis.
  • Now, select cell C15 to insert the CHOOSE function to choose a specific range of data at a time in the chart.
=CHOOSE($B$26,C4,D4,E4,F4)
  • To apply and activate the Option Button go to Developer >> Insert >> Option Button and link the button to Index Number.
  • To link the option buttons right-click the button, and choose Format Control >> Control >> Checked.
  • Last, right down the cell name and lock the cell to complete the process.

Getting specific range from chart by applying CHOOSE function

Note: Here, cell B26 is the index number, and C4, D4, E4, and F4 are values. The value cells are not locked, so drag down the formula until cell C23.


How to Use Other Functions Instead of CHOOSE Function: 2 Simple Examples

The CHOOSE function is used to select the required range or value. Instead of using the CHOOSE function, we can also use the SWITCH function and the nested IF function to execute a similar output.

Example 1: Using the SWITCH Function as a Reminder

Here, we will use the SWITCH function instead of the CHOOSE function to get the weekday and month from a date.

  • First, select cell D5 and get the weekday as output.
=SWITCH(WEEKDAY(C5),1,"Saturday",2,"Sunday",3,"Monday",4,"Tuesday",5,"Wednesday",6,"Thursday",7,"Friday")

Using SWITCH function instead of CHOOSE function

  • Here, the method of executing this process is like the earlier method but instead of using the CHOOSE function, we used the SWITCH function.
  • Last, insert another formula in cell E5 to get the month name from the date.
=SWITCH(MONTH(C5),1,"January",2,"February",3,"March",4,"April",5,"May",6,"June",7,"July",8,"August",9,"September",10,"October",11,"November",12,"December")

Using SWITCH function to get the month name


Example 2: Applying the Nested IF Function to Get Required Value

In this example, we will use the nested IF function instead of the CHOOSE function.

  • If the function doesn’t match the criteria, then the formula will return the value as No Match otherwise the formula will return the value according to the value in cell B15.
=IF(B15>9,"NoMatch",IF(B15=8,D12,IF(B15=7,D11,IF(B15=6,D10,IF(B15=5,D9,IF(B15=4,D8,IF(B15=3,D7,IF(B15=2,D6,IF(B15=1,D5,"No Match")))))))))

Applying the Nested IF Function instead of CHOOSE function

Formula Breakdown

IF(B15>9,”NoMatch”

  • Here, if the value of cell B15 is greater than then the output will be “No Match”.

IF(B15=8,D12

  • In this part, the data will return a value as cell D12 if cell B15 is 8.
  • and this nested function will continue its loop until the value of cell B15 is 1.

IF(B15>9,”NoMatch”,IF(B15=8,D12,IF(B15=7,D11,IF(B15=6,D10,IF(B15=5,D9,IF(B15=4,D8,IF(B15=3,D7,IF(B15=2,D6,IF(B15=1,D5,”No Match”)))))))))

  • This formula will return value according to the mentioned cell till the value in cell B15 is between 1-8. Otherwise, the output is “No Match”.

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


Frequently Asked Questions

Q1: What is the syntax of the CHOOSE function?

Ans: The syntax of the CHOOSE function is CHOOSE(index_num,value1,[value2],[value3],[value4],…).

Q2: How can I use the CHOOSE function to create a drop-down list?

Ans: First you need to use the data validation to create a drop-down list. Then enter the choose function to get the selected options along with values.

Q3: What is the SWITCH or CHOOSE function in Excel?

Ans: The SWITCH function returns the value from the list according to the first matching value and the CHOOSE function returns the value from the given list according to the index number.


Things to Remember

  • While using the CHOOSE function the index number should be always a positive whole number and should correspond to the values.
  • There is a value limit in the CHOOSE function the lowest value is 1 and the highest value is 254.

Download Practice Workbook

You may download the workbook for practice.


Conclusion

In this article, we learned how to use the Excel CHOOSE function, and if the CHOOSE function is not available, how to use the alternative functions. We covered everything regarding the CHOOSE function and alternative functions as well. Hopefully, you can solve the problem shown in this article.


Excel CHOOSE Function: Knowledge Hub


<< Go Back to Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Afrina Nafisa
Afrina Nafisa

Afrina Nafisa Alam, BSc, Industrial and Production Engineering from Ahsanullah University of Science and Technology. She has been working with the Exceldemy project for over 6 months and is currently a web content developer here. She has published over 18 articles and reviewed several during this period. She is keen to learn different features and deliver the knowledge in her current project. She is interested in learning different features of Microsoft Office, especially Excel, Power Query, Data Analysis,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo