How to Use CHOOSE Function in Excel?

Below is an overview of our sample dataset.

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

The CHOOSE function will return the value from the list at a specific position. If you select a range of student IDs and write 8, this function will return the value of the 5th position.

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

Example 2 – Applying the CHOOSE Function to Get the Sum

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

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

• Select cell E14 to apply the formula.

• Select cell B19 to apply for the index number and lock the cell.
• Enter the criteria and complete the formula.
• Enter another formula using the CHOOSE function, the LARGE function and the SMALL function to get the values of the selected criteria as below.

• Drag down the Fill handle to get all three highest or lowest values.

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

• Enter the formula to get the days in cell D5.

• The CHOOSE function will return the selected date and the WEEKDAYS function will return the day of the selected date.
• Get the month name with the MONTH function using the same process already used in “Weekday”.

Read More: Advanced Uses of CHOOSE Function in Excel

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

• Select cell C15 and enter the function below.

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

Example 6 – Applying RANDBETWEEN Function to Get Random Values

• The RANDBETWWEN function returns a random value here and the CHOOSE function chooses the value to get the required output.

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

• Create a chart (go to Insert >> Chart) selecting Student ID as X-axis. and the subjects as Y-axis.
• Select cell C15 to insert the CHOOSE function to choose a specific range of data at a time in the chart.

• 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.
• Right down the cell name and lock the cell to complete the process.

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

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

Example 1: Using the SWITCH Function as a Reminder

• Select cell D5 and get the weekday as output.

• The method of executing this process is like the earlier method but instead of using the CHOOSE function, we used the SWITCH function.
• Insert another formula in cell E5 to get the month name from the date.

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

• If the function doesn’t match the criteria, the formula will return the value as No Match otherwise the formula will return the value according to the value in cell B15.

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

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

Excel CHOOSE Function: Knowledge Hub

• How to Use CHOOSE Function with Array in Excel
• How to Apply CHOOSE Function to Create Drop-Down List in Excel
• How to Use CHOOSE Function in Excel for Scenarios
• How to Use Excel Formula to Choose Between Two Values

<< Go Back to Excel Functions | Learn Excel