The tutorial explains the syntax and basic uses of the CHOOSE function and provides a few nontrivial examples showing how to use a CHOOSE formula in Excel.
CHOOSE is one of those Excel functions that may not look useful on their own, but combined with other functions give a number of awesome benefits. At the most basic level, you use the CHOOSE function to get a value from a list by specifying the position of that value. Further on in this tutorial, you will find several advanced uses that are certainly worth exploring.
The CHOOSE function is an incredibly useful tool that provides dynamic flexibility within your Excel formulas. With CHOOSE, you can return different values from a predefined list based on an index number.
In this comprehensive guide, I’ll explain stepbystep how to use the CHOOSE function in Excel. You’ll learn how to create dynamic formulas that can adjust on the fly based on a changing index Let’s dive in!
What Does the CHOOSE Function Do in Excel?
The CHOOSE function allows you to specify a list of potential values as the function arguments. It will return whichever value corresponds to the index number provided.
For example, imagine you have a list of sales bonus percentages:
5%, 10%, 15%, 20%, 25%
You want to return one of those percentages based on a sales rep’s performance ranking
So if they are ranked 3, it should return 15%.
This is where CHOOSE comes in handy! It would look like:
=CHOOSE(3,5%,10%,15%,20%,25%)
By making the index dynamic, you can create flexible formulas that change output based on variable input.
How to Use the CHOOSE Formula in Excel
Using the CHOOSE function in Excel involves just 3 simple steps:

Insert the CHOOSE function. Select the cell where you want the returned value to appear. Type
=CHOOSE(
and the opening parenthesis. 
Type your list of values. The first argument after “CHOOSE(” is the index number, but it’s often easier to first enter the list of potential values you want the formula to choose from. Separate each value by a comma.

Enter the index number. The index tells the function which value to return. Index 1 would return the first value, 2 the second value, and so on. Close the function with a final parenthesis.
And that’s it! Those three steps are all you need to use the CHOOSE function in Excel.
Let’s look at a more detailed example…
CHOOSE Function Example
Say you have a list of product prices based on quantity tier:
 1100 units = $5 each
 101500 units = $4 each
 501+ units = $3 each
You want to create a formula where you can input the quantity purchased, and it will automatically return the right price per unit.
Here are the steps to use CHOOSE for this:

In cell B2, input the quantity purchased, like 250.

In C2, insert the CHOOSE function:
=CHOOSE(

Add your list of potential values separated by commas:
=CHOOSE(B2,$5,$4,$3)
 Close the function:
=CHOOSE(B2,$5,$4,$3)
Now cell C2 will display the price per unit based on the quantity in B2!
If you change the quantity, the price will automatically adjust thanks to the dynamic CHOOSE formula.
Tips for Using CHOOSE in Excel
When using the CHOOSE function, keep these tips in mind:

Lists can contain numbers, text, cell references, or calculations.

Nest CHOOSE within other functions like IF or VLOOKUP to extend functionality.

Watch out for offbyone errors – double check your index number corresponds to the intended return value.

Error checking will display #VALUE! if index is <1 or > number of choices.

Return blank cells instead of 0 with IF(ISNA(CHOOSE(),””,CHOOSE()) construction.

Use CHOOSE to rank and assign letter grades, select colors dynamically, choose icons based on score, and more!

For very long lists, reference cells instead of typing values directly to keep formulas clean.

You can replicate CHOOSE with a nested IF statement but it’s messier and harder to maintain.

When copying formulas, lock cell references with $ to prevent incorrect indices.
With creative use cases, the CHOOSE function provides endless possibilities for dynamic lookup formulas in Excel.
Common Uses for the CHOOSE Function
Beyond simple lists, what are some common realworld uses for the CHOOSE function in Excel?
Grading System
Use CHOOSE to assign letter grades based on numeric test scores:
=CHOOSE(CEILING(B2/10), "F","D","C","B","A")
Flooring the score divides it into 10 point buckets, rounding up. Then the grade text is returned.
Event Calendar
Pull event names into a calendar based on numeric date codes:
=CHOOSE(B2, "Birthday","Anniversary","Graduation","Holiday")
The date codes can be used as the index to return the matching event.
Commission Rates
Return commission percentages based on sales volume tier:
=CHOOSE(B2, 5%, 7%, 10%, 12%, 15%)
The higher the volume, the higher percentage they earn.
Dynamic Chart Colors
Color a chart series dynamically based on cell value:
=CHOOSE(B2, "Red","Orange","Yellow","Green","Blue")
The color text can be used in conditional formatting rules.
Patient Status
Classify patient status using categories:
=CHOOSE(CEILING(B2,0.5),"Critical","Serious","Fair","Good","Excellent")
Rounds the input score to assign a status category.
The possibilities are endless!
Troubleshooting Errors in CHOOSE Formulas
If you run into issues with the CHOOSE function returning errors or incorrect values, here are some troubleshooting tips:

Double check the index number matches the intended return value position. Offbyone mistakes are common.

Make sure the index number is within the valid range for the number of arguments provided (1 to 256).

Confirm cell references are locked properly when copying formulas to prevent shifting indices.

Ensure all commas separating arguments are in place – missing commas will cause #VALUE errors.

Try manually typing values instead of pointing to cell references to isolate the problem.

Wrapping CHOOSE in IFERROR can display a custom message instead of #VALUE! errors.

Start simple and add nested functions one at a time to identify where issues arise.
Following logical troubleshooting steps will help narrow down and resolve any issues with CHOOSE formulas.
Next Level: Creative Ways to Use CHOOSE in Excel
The CHOOSE function is ready to take your Excel skills to the next level. Here are some creative examples of advanced formulas with CHOOSE:

Nested IF statements to check multiple criteria before selecting the CHOOSE index.

LOOKUP functions to search categories and find the right index value.

ROUND or PRODUCT to manipulate the index value into the needed range.

MIN/MAX to constrain the index within upper and lower limits.

MID or LEFT/RIGHT to extract just a portion of returned text values.

CONCATENATE or & to combine chosen text items into full sentences.

SUBSTITUTE to swap words within chosen text strings.

Use CHOOSE to generate randomized data sets for analysis.
The key is imagining how CHOOSE can add flexibility within your existing formulas. The function really shines when you combine it creatively with other Excel tools.
Should You Use CHOOSE or a Nested IF?
A common question is when to use CHOOSE vs a nested IF statement. While both can provide similar output, here are some key differences:

CHOOSE formulas are simpler and easier to read vs long nested IFs.

CHOOSE allows you to easily maintain and add/remove options.

Nested IFs allow more complex conditional logic before returning a value.

CHOOSE has a limit of 256 arguments while nested IFs have no limit.

CHOOSE avoids repetition of long IF statements checking the same criteria.

CHOOSE avoids the extensive troubleshooting that comes with nested IFs.
In general, I would recommend CHOOSE for ease of use, flexibility, and maintenance. But nested IFs provide more custom control for complex logic requirements. Evaluate both to see which option makes more sense for your specific needs.
The CHOOSE function is an incredibly versatile tool that every Excel user should have in their formula toolkit. With the ability to return different values from a list based on an index number, CHOOSE provides dynamic flexibility to your models.
Now you have a clear, stepbystep understanding of how to use CHOOSE in Excel, from basic examples to advanced creative applications.
Put these skills into practice and CHOOSE will soon become your new goto function for flexible lookup formulas. Your Excel skills will reach new
How to use CHOOSE function in Excel – formula examples
The following examples show how CHOOSE can extend the capabilities of other Excel functions and provide alternative solutions to some common tasks, even to those that are considered unfeasible by many.
Excel CHOOSE formula to generate random data
As you probably know, Microsoft Excel has a special function to generate random integers between the bottom and top numbers that you specify – RANDBETWEEN function. Nest it in the index_num argument of CHOOSE, and your formula will generate almost any random data you want.
For example, this formula can produce a list of random exam results:
=CHOOSE(RANDBETWEEN(1,4), "Poor", "Satisfactory", "Good", "Excellent")
The formulas logic is obvious: RANDBETWEEN generates random numbers from 1 to 4 and CHOOSE returns a corresponding value from the predefined list of four values.
Note. RANDBETWEEN is a volatile function and it recalculates with every change you make to the worksheet. As the result, your list of random values will also change. To prevent this from happening, you can replace formulas with their values by using the Paste Special feature.
How to use the CHOOSE function
What is choose function in Excel?
This article describes the formula syntax and usage of the CHOOSE function in Microsoft Excel. Uses index_num to return a value from the list of value arguments. Use CHOOSE to select one of up to 254 values based on the index number.
How to use select function in Excel?
Start by opening Excel and either create a worksheet where you want to use the CHOOSE function. Input or identify the data you will work with using the CHOOSE function. This is where you define the parameters that will be used to select your outcome. Here’s a simple dataset with 10 rows that we’ll use to demonstrate the CHOOSE function:
What is a choice function in JavaScript?
value1 – The first value from which to choose. value2 – [optional] The second value from which to choose. The CHOOSE function returns a value from a list using a given position or index. The values provided to CHOOSE can be hardcoded constants or cell references. The first argument for the CHOOSE function is index_num.
How to select a value in Excel?
The CHOOSE Excel formula is: where, index_num: An integer that indicates the value argument. This argument must be any value from 1 to 254 or a cell reference to a cell having a number between 1254. value1, value2,…: The list of 1254 values from which the CHOOSE function selects a value based on the specified index_num.