Excel CHOOSE Function
last modified October 5, 2026
The CHOOSE function is a versatile lookup function in Excel that
returns a value from a list based on a specified position. This tutorial
provides a comprehensive guide to using the CHOOSE function with
detailed examples. You'll learn basic syntax, practical applications, and
advanced techniques to master this powerful Excel function.
CHOOSE Function Basics
The CHOOSE function selects and returns a value from a list of
arguments based on an index number. It can handle up to 254 values and is
useful for creating dynamic formulas.
| Component | Description |
|---|---|
| Function Name | CHOOSE |
| Syntax | =CHOOSE(index_num, value1, [value2], ...) |
| Arguments | Index number + 1-254 values |
| Return Value | Selected value from the list |
This table breaks down the essential components of the CHOOSE
function. It shows the function name, basic syntax format, argument limits, and
return value characteristics.
| Argument | Meaning |
|---|---|
| index_num | A number from 1 to 254 that selects which value to return. It can be a
literal, a cell reference, or a formula. A value below 1 or greater than the
number of supplied values returns #VALUE!. |
| value1 | The first value to choose from. This argument is required. |
| value2, ... | Additional values, up to 254 in total. Each value can be a number, text, a logical value, a cell reference, or a range. |
Only index_num and value1 are required. If
index_num is a decimal, Excel truncates it to an integer before
choosing: =CHOOSE(1.9, "a", "b") returns "a", because
1.9 is truncated to 1.
Basic CHOOSE Example
This example demonstrates the simplest use of the CHOOSE function with hard-coded values.
=CHOOSE(2, "Apple", "Banana", "Cherry")
This formula returns "Banana" because it's the second item in the list (index 2). The first argument is the position number, followed by the values to choose from.
CHOOSE with Cell References
A more practical use of CHOOSE involves referencing cells for both
the index and values. Here's an example with cell references.
| A | B | C |
|---|---|---|
| 2 | Red | |
| Green | ||
| Blue | ||
| =CHOOSE(A1, B1, B2, B3) |
The table shows a simple spreadsheet with an index number in A1 and color values in B1:B3. The CHOOSE formula in C4 returns the color based on the index.
=CHOOSE(A1, B1, B2, B3)
This formula uses the value in A1 (2) to select from B1 ("Red"), B2 ("Green"), and B3 ("Blue"). The result will be "Green". This demonstrates how CHOOSE can create dynamic outputs.
CHOOSE with Ranges
CHOOSE can return entire ranges, which is useful for creating dynamic references in other formulas. This example shows this capability.
| A | B | C |
|---|---|---|
| 2 | Q1 | 100 |
| Q2 | 200 | |
| Q3 | 300 | |
| =SUM(CHOOSE(A1, B1:C1, B2:C2, B3:C3)) |
This table demonstrates how CHOOSE can select different ranges based on an index. The formula in C4 sums either Q1, Q2, or Q3 data based on the value in A1.
=SUM(CHOOSE(A1, B1:C1, B2:C2, B3:C3))
If A1 contains 2, this formula selects the range B2:C2 ("Q2" and 200) and sums it. Since "Q2" is text, it's ignored, returning 200. This technique is powerful for dynamic data analysis.
CHOOSE with Nested Functions
CHOOSE can be nested with other functions to create complex logic. This example shows CHOOSE nested with WEEKDAY.
| A | B |
|---|---|
| 5/15/2023 | |
| =CHOOSE(WEEKDAY(A1), "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat") |
The table demonstrates using CHOOSE to convert a weekday number to a day name. The WEEKDAY function returns a number 1-7, which CHOOSE uses to select the correct day abbreviation.
=CHOOSE(WEEKDAY(A1), "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat")
For May 15, 2023 (a Monday), WEEKDAY returns 2, so CHOOSE returns "Mon". This combination is useful for creating user-friendly date displays without complex formatting.
CHOOSE for Scenario Analysis
CHOOSE is excellent for scenario analysis where you need to switch between different calculation methods. This example demonstrates this application.
| A | B | C |
|---|---|---|
| 2 | 1000 | |
| =B1*CHOOSE(A1, 0.1, 0.15, 0.2) |
This table shows how CHOOSE can select different percentage rates based on a scenario number. The formula in C2 calculates different commission rates depending on the value in A1.
=B1*CHOOSE(A1, 0.1, 0.15, 0.2)
With A1=2 and B1=1000, this formula calculates 1000 * 0.15 = 150. This approach lets you easily switch between different calculation parameters without complex IF statements.
CHOOSE with VLOOKUP Alternative
CHOOSE can create a simplified lookup table as an alternative to VLOOKUP for small datasets. This example shows this technique.
| A | B | C |
|---|---|---|
| 3 | ||
| =CHOOSE(A1, "Bronze", "Silver", "Gold", "Platinum") |
The table demonstrates using CHOOSE as a simple lookup where numeric codes (1-4) correspond to membership levels. This is more efficient than VLOOKUP for small, static lists.
=CHOOSE(A1, "Bronze", "Silver", "Gold", "Platinum")
When A1 contains 3, this formula returns "Gold". While limited to about 254 items, this method is faster to set up than VLOOKUP for small, unchanging lookup tables.
CHOOSE with Arrays
The index_num argument can itself be an array. When it is, CHOOSE
returns an array that holds one chosen value for each index. This lets a single
formula return several results at once.
=CHOOSE({1,2}, "a", "b") ' returns {"a","b"}
=SUM(CHOOSE({1,3}, 10, 20, 30)) ' returns 40 (10 + 30)
In Microsoft 365 and Excel 2021 the result spills into the neighbouring cells
automatically. In Excel 2019 and earlier, array entry is required, or the array
can be wrapped in a function such as SUM that consumes it directly.
CHOOSE with MATCH
A common pattern combines MATCH to find a position with
CHOOSE to return a matching label. This builds a compact lookup
without a helper table.
| A | B |
|---|---|
| Gold | =CHOOSE(MATCH(A1, {"Bronze","Silver","Gold","Platinum"}, 0), "Low", "Mid", "High", "VIP") |
MATCH locates "Gold" at position 3 inside the array constant, so
CHOOSE returns the third label, "High".
=CHOOSE(MATCH(A1, {"Bronze","Silver","Gold","Platinum"}, 0), "Low", "Mid", "High", "VIP")
CHOOSE Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| index_num is less than 1 | #VALUE! error | Index positions start at 1, not 0 |
| index_num is greater than the number of values | #VALUE! error | Add the missing values or cap the index |
| index_num is non-numeric text such as "abc" | #VALUE! error | Convert the index with VALUE or ensure the cell is numeric |
| More than 254 values supplied | Formula is rejected | Use a lookup table instead of CHOOSE |
| A returned range cannot spill | #VALUE! error in one cell | Wrap the result in SUM, or let it spill in a modern Excel version |
Most CHOOSE errors are boundary problems: an index of 0, a
fractional index that truncates to 0, or an index that points past the last
value. When the index is computed, guard it with MIN and
MAX, for example =CHOOSE(MAX(1, MIN(A1, 3)), "Low", "Mid", "High"),
so the formula can never step outside the available list.
The CHOOSE function is a versatile tool for creating dynamic
formulas in Excel. From simple value selection to complex scenario analysis,
CHOOSE offers elegant solutions. It can return numbers, text,
logical values, and even whole ranges, which makes it useful for switching
between data sets, mapping codes to labels, and building array results.
Mastering its various applications will significantly improve your spreadsheet
efficiency. Remember that CHOOSE is limited to 254 values, requires
numeric index positions starting from 1, and truncates a decimal index before
selecting.
Author
List all Excel Formulas.