Lookup & reference
How to Use CHOOSE in Excel (Pick from a List)
CHOOSE returns one value from a list based on a position number. Give it 2 and a list of options, and it hands back the second one, which makes it a clean way to turn a number into a label, a month, or a quarter.
It is simpler than a long nested IF and pairs neatly with functions like WEEKDAY and MATCH. This guide covers the exact syntax, real worked examples, and the errors to avoid.
CHOOSE syntax
=CHOOSE(index_num, value1, [value2], ...) | Argument | Required | What it does |
|---|---|---|
index_num | Required | Which item to return: 1 picks value1, 2 picks value2, and so on. Must be between 1 and the count of values. |
value1 | Required | The first item in the list. Can be text, a number, a cell reference, or a formula. |
value2 | Optional | The second item, and you can keep adding values up to 254 in total. |
Note: index_num must be a whole number from 1 to the number of values, otherwise CHOOSE returns #VALUE!. Works the same in Google Sheets.
Examples
Real, copy-paste-ready formulas.
1. Pick an item by number
=CHOOSE(3, "Mon", "Tue", "Wed", "Thu", "Fri") The index 3 returns the third value, "Wed". Swap the 3 for a cell reference and the result follows whatever number that cell holds.
2. Turn a rating number into a label
=CHOOSE(B2, "Low", "Medium", "High") CHOOSE maps the number in B2 straight to a label, which is cleaner and easier to read than a nested IF when each number has one fixed result.
3. Convert a date to a day name
=CHOOSE(WEEKDAY(A2), "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat") WEEKDAY returns 1 to 7, and CHOOSE turns that number into the matching day name. Keep the list in the same Sunday-first order WEEKDAY uses.
4. Map a month number to a quarter
=CHOOSE(B2, "Q1", "Q1", "Q1", "Q2", "Q2", "Q2", "Q3", "Q3", "Q3", "Q4", "Q4", "Q4") Listing all 12 months lets CHOOSE map each one to its quarter directly, with no date math or nested IF needed.
How to write CHOOSE step by step
- 1
Click the result cell and type =CHOOSE(
- 2
Type the index number, or click the cell that holds it, then a comma.
- 3
Type the first option (in quotes if it is text), then a comma.
- 4
Keep adding options separated by commas, in the order you want them numbered.
- 5
Close the bracket ) and press Enter.
Common errors and fixes
| Error | Why it happens | How to fix it |
|---|---|---|
#VALUE! | index_num is less than 1, greater than the number of values, or is not a number. | Make sure the index is a whole number between 1 and the count of options. Wrap it in INT() if it might be a decimal. |
Returns the wrong item | The options are listed in a different order than the numbers they should map to. | Put the values in the exact order the index expects, so position 1 is value1, position 2 is value2, and so on. |
#N/A from a paired function | A function feeding index_num, such as MATCH, did not find its value. | Fix the inner function first, or wrap the whole formula in IFERROR for a friendly fallback. |
Missing options error | index_num points at a position you never supplied, for example 5 when only 4 values are listed. | Add the missing options, or reduce the index so it stays within the list. |
Frequently asked questions
When should I use CHOOSE instead of IF?
Use CHOOSE when a number maps directly to one fixed result, such as 1 to 3 becoming Low, Medium, High. It is shorter and clearer than a chain of nested IFs because there is no condition to repeat.
How many values can CHOOSE hold?
CHOOSE accepts up to 254 values. The index_num must be a whole number between 1 and the number of values you have actually supplied.
Can CHOOSE return a range or a formula?
Yes. Each value can be a cell reference, a range, or another formula, not just text. This is how CHOOSE is used inside lookups to reorder columns for a left lookup.
Does CHOOSE work in Google Sheets?
Yes. CHOOSE works identically in Google Sheets, including pairing it with WEEKDAY, MONTH, or MATCH to drive the index number.