CHOOSE returns a value from a list based on a numeric position you supply. It is a simple lookup tool for selecting one of several options without complex nested IFs.
=CHOOSE(index_num, value1, [value2], …)What does CHOOSE do?
CHOOSE is a Lookup & Reference function that returns a value from a list of options based on a number you provide. You give it an index number (1, 2, 3, and so on) and a list of values; it simply counts to that position and hands back the matching value. Think of it as a numbered menu where you order by number instead of by name.
This matters because many everyday spreadsheet tasks involve picking one option from a short list: choosing a quarter's label, mapping a month number to a name, or selecting a commission rate based on a tier. CHOOSE keeps those formulas short and readable, avoiding long chains of IF statements that are hard to audit and easy to break.
A common real situation is a sales report where you want to show a different metric depending on a drop-down selection. Instead of writing separate formulas for each metric, you can use CHOOSE with the drop-down's position number to return the right column's value. It is also handy for building simple dashboards, grade converters, and status labels.
When to use CHOOSE
- Converting a month number (1–12) into a month name.
- Returning a label or category based on a numeric code.
- Selecting one of several calculated values in a dashboard drop-down.
- Mapping a grade score to a letter grade or comment.
- Picking a commission rate or discount tier by rank.
- Choosing a day name from a weekday number.
CHOOSE syntax
=CHOOSE(index_num, value1, [value2], …)| Argument | Required? | What to enter |
|---|---|---|
index_num | Required | The position number of the value you want. Must be between 1 and the number of values provided. Example: 2 returns the second value. |
value1 | Required | The first value or cell reference in the list. Example: A2 or "North". |
value2 | Optional | The second value or cell reference, and so on. You can supply up to 254 values. Example: B2. |
What it returns: The value at the position specified by index_num. The return type matches the selected value (number, text, date, or reference).
CHOOSE examples
1 Pick a month name from a number
The goal is to turn a month number into its name. The formula =CHOOSE(A2,"January","February","March","April","May","June","July","August","September","October","November","December") uses the number in A2 as the position. Because A2 contains 3, CHOOSE counts to the third value and returns "March". This is a quick way to label reports without a lookup table.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month Number | Month Name | |||
| 2 | 3 | March | |||
| 3 | 7 | ||||
| 4 | 12 | ||||
| 5 |
2 Return a day name from a weekday number
Here you want the day name for a weekday number where 1 is Monday. The formula =CHOOSE(A2,"Monday","Tuesday","Wednesday","Thursday","Friday","Saturday","Sunday") reads the number in A2. Since A2 is 5, it returns the fifth value, "Friday". This pattern works for any small fixed list where order matters.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Weekday Number | Day Name | |||
| 2 | 5 | Friday | |||
| 3 | 1 | ||||
| 4 | 7 | ||||
| 5 |
3 Choose a commission rate by sales tier
A sales team uses four commission tiers, and the tier number is stored in column A. The formula =CHOOSE(A2,0.02,0.04,0.06,0.08) picks the rate for the tier in A2. With A2 equal to 3, CHOOSE returns 0.06, the third rate. You can then multiply this rate by a sales amount to calculate commission.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Tier | Sales Amount | Commission Rate | |||
| 2 | 3 | 45000 | 0.06 | |||
| 3 | 1 | 12000 | ||||
| 4 | 4 | 80000 | ||||
| 5 |
4 Select a metric for a dashboard drop-down
A dashboard has a drop-down that returns a position number in A2: 1 for Sales, 2 for Cost, 3 for Profit. The formula =CHOOSE(A2,B2,C2,D2) returns the value from the matching column for the current row. If A2 is 2, it returns the Cost value from C2. This lets one formula display different metrics without editing the sheet.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Metric Position | Sales | Cost | Profit | |||
| 2 | 2 | 15000 | 9000 | 6000 | 9000 | ||
| 3 | 1 | 22000 | 14000 | 8000 | |||
| 4 |
5 Map a score to a letter grade
A teacher stores a grade position in A2, where 1 is A, 2 is B, 3 is C, and 4 is D. The formula =CHOOSE(A2,"A","B","C","D") returns the letter for that position. With A2 equal to 2, the result is "B". This keeps grade labels consistent and easy to change in one place.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Grade Position | Student | Score | |||
| 2 | 2 | Alex | 84 | B | ||
| 3 | 1 | Priya | 92 | |||
| 4 | 3 | Sam | 76 | |||
| 5 |
Common CHOOSE errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #VALUE! | index_num is not a number, for example text or a blank cell. | Make sure index_num refers to a cell containing a number, or wrap it with a function like VALUE if it is stored as text. |
| #VALUE! | index_num is less than 1 or greater than the number of values supplied. | Check that the index is between 1 and the count of values; use IFERROR or validation to keep it in range. |
| #NAME? | The function name is misspelled, such as CHOSE. | Type CHOOSE exactly and let Excel's formula autocomplete help. |
| #REF! | A value argument refers to a deleted cell or sheet. | Update the reference to a valid cell or range, or undo the deletion. |
Tips for using CHOOSE
- Keep the list of values in a logical order and document the position numbers so others can maintain the formula.
- Use cell references instead of hard-coded values when the options may change, so updates happen in one place.
- Combine CHOOSE with
MATCHto turn a text label into a position number automatically. - Remember that CHOOSE evaluates all value arguments, so avoid expensive calculations in the list if performance matters.
- For large lists, a lookup table with
VLOOKUPorXLOOKUPis often easier to maintain than a long CHOOSE formula.
Frequently asked questions
What is the difference between CHOOSE and IF?
CHOOSE selects by position number from a list, while IF tests a condition and returns one of two results. CHOOSE is shorter when you have several fixed options and a number already tells you which one to use.
Can CHOOSE return a cell reference instead of a value?
Yes. If you supply cell references as the value arguments, CHOOSE returns the reference, and functions like SUM can use it. For example, =SUM(CHOOSE(2,A1:A5,B1:B5)) sums the second range.
How many values can CHOOSE handle?
Up to 254 value arguments. If you need more, consider a lookup table instead.
Why does CHOOSE return #VALUE! when my index looks correct?
The index may be stored as text, or it may be outside the range 1 to the number of values. Check the cell's data type and the count of values.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. CHOOSE reference